写入Range.FormulaArray给我一个#NAME！错误 [英] Writing into Range.FormulaArray gives me a #NAME! error

问题描述

这里没有什么真正的想法，因为我需要将公式拆分为2，因为它超过255个字符（来源： http://www.dicks-blog.com/archives/2005/01/10/entering-long-array-formulas-in -vba / http://support.microsoft.com/kb/213181 ） 。

  f1 == SI（1 = 1，SOMME（SI（mySheet！$ R：$ R = ，SI（mySheet！$ AQ：$ AQ = AM $ 1，NBCAR（Incidents！$ AP：$ AP）-NBCAR（SUBSTITUE（mySheet！$ AP：$ AP，$ B2，））） ）/ NBCAR（$ B2）-X_X_X（））
 f2 =SOMME（SI（mySheet！$ R：$ R =，SI（mySheet！$ AQ：$ AQ = SI（mySheet！$ AM：$ AM = $ A2，SI（DROITE（mySheet！$ AP：$ AP，6）= $ B2,1））））），
 .Range AM2）。FormulaArray = f1 
 .Range（AM2）替换X_X_X（），f2 
  

公式是法语，不是很重要。公式在单元格AM2中正确返回，所以这里是结果：

  = {SOMME（SI（1 = 1; SI（mySheet！$ R：$ R =something; SI（mySheet！$ AQ：$ AQ = AM $ 1; NBCAR（mySheet！$ AP：$ AP）-NBCAR（SUBSTITUE（mySheet！$ AP：$ AP; $）;）））/ NBCAR（$ B2）-SOMME（SI（mySheet！$ R：$ R =something; SI（mySheet！$ AQ：$ AQ = AM $ 1; SI（mySheet！ $ AM：$ AM = $ A2; SI（DROITE（mySheet！$ AP：$ AP; 6）= $ B2; 1）））））;）} 
  / pre> 
 
 
但是，我得到一个#NAME！错误。如果我查看公式调试，错误来自我的第一个函数，Excel不能解释 SI（）（这是法语为 IF（））。我尝试完全删除那个 IF（）子句，但Excel不能解释现在的第一个函数 SOMME（）（ SUM（））。 
 
 
 
如果我只需点击公式栏，然后按CTRL + Shift + Enter（不更改任何内容），则返回正确的值。
 
 
 
为什么在VBA设置数组公式后Excel不会给我正确的数字？如果我只是在Excel中重新提交公式，为什么会起作用？为什么第一个功能总是有问题？我想有一些其他限制，我不知道。
 
 
 
我在浏览网页时发现另一个类似的问题，但没有解决方案。
解决方案
因为Excel具有英文和 .FormulaLocal中的公式的 .Formula  用于Excel界面语言的公式。
 
 
 
您需要本地版本，但不存在阵列。你必须用英文提供数组公式。
 
 
 
将此法式公式设置为临时（隐藏）范围的 .FormulaLocal ，然后从相同范围读回 .Formula 并将其设置为所需范围内的 .FormulaArray 。但是您不应该，因为您的代码不会在法语之外的任何Excel版本上工作。最便携的东西是在您的代码中始终使用英文公式。
 
Nothing really fancy here, altough I need to split the formula in 2 because it is longer than 255 characters (sources: http://www.dicks-blog.com/archives/2005/01/10/entering-long-array-formulas-in-vba/ http://support.microsoft.com/kb/213181).
    f1 = "=SI(1=1,SOMME(SI(mySheet!$R:$R = ""something"",SI(mySheet!$AQ:$AQ = AM$1, NBCAR(Incidents!$AP:$AP)-NBCAR(SUBSTITUE(mySheet!$AP:$AP,$B2,"""")))))/NBCAR($B2)-X_X_X())"
    f2 = "SOMME(SI(mySheet!$R:$R = """",SI(mySheet!$AQ:$AQ = AM$1, SI(mySheet!$AM:$AM = $A2,SI(DROITE(mySheet!$AP:$AP,6) = $B2,1))))),"""""
    .Range("AM2").FormulaArray = f1
    .Range("AM2").Replace "X_X_X()", f2
The formulas are in French, which shouldn't really matter. The formula is correctly returned in the cell AM2, so here is the result:
={SOMME(SI(1=1;SI(mySheet!$R:$R = "something";SI(mySheet!$AQ:$AQ = AM$1; NBCAR(mySheet!$AP:$AP)-NBCAR(SUBSTITUE(mySheet!$AP:$AP;$B2;"")))))/NBCAR($B2)-SOMME(SI(mySheet!$R:$R = "something";SI(mySheet!$AQ:$AQ = AM$1; SI(mySheet!$AM:$AM = $A2;SI(DROITE(mySheet!$AP:$AP;6) = $B2;1)))));"")}
However, I get a #NAME! error. If I look into the formula debug, the error comes from my first function, Excel isn't able to interpret SI() (which is the French for IF()). I tried completely removing that IF() clause but then Excel can't interpret the first function which is now SOMME() (SUM()). 

If I just click into the formula bar and press CTRL+Shift+Enter (not changing anything), it returns the correct value.

Why doesn't Excel give me the right number after VBA sets the array formula? Why does it work if I just resubmit the formula in Excel? And why is it always having trouble with the first function? I guess there is some other kind of limitation that I am not aware of.

I found another similar issue while browsing the Web but no solution.
解决方案
Because Excel has .Formula for formulas in English and .FormulaLocal for formulas in the language of the Excel interface.

You need the local version, but there isn't one for arrays. You have to provide array formulas in English.

You could e.g. set this French formula to .FormulaLocal of a temporary (hidden) range, then read back .Formula from the same range and set that as .FormulaArray on the desired range. But you shouldn't, because your code won't work on any Excel version other than French. The most portable thing is to always use English formulas in your code.

                        
这篇关于写入Range.FormulaArray给我一个#NAME！错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！