写入 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).
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
公式是法语的，这应该无关紧要.公式在单元格 AM2 中正确返回，因此结果如下:
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)))));"")}
但是，我得到了一个#NAME！错误.如果我查看公式调试，错误来自我的第一个函数，Excel 无法解释 SI()(这是 IF() 的法语).我尝试完全删除 IF() 子句，但 Excel 无法解释现在是 SOMME() (SUM()).
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()).
如果我只是单击编辑栏并按 CTRL+Shift+Enter(不更改任何内容)，它会返回正确的值.
If I just click into the formula bar and press CTRL+Shift+Enter (not changing anything), it returns the correct value.
为什么在 VBA 设置数组公式后 Excel 没有给我正确的数字?如果我只是重新提交 Excel 中的公式，为什么它会起作用?为什么第一个函数总是有问题?我想还有一些我不知道的其他限制.
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.
推荐答案
因为 Excel 有 .Formula 用于英文公式和 .FormulaLocal 用于公式的语言Excel界面.
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.
你可以例如将此法语公式设置为临时(隐藏)范围的 .FormulaLocal，然后从同一范围读回 .Formula 并将其设置为 .FormulaArray> 在所需的范围内.但您不应该这样做，因为您的代码不适用于法语以外的任何 Excel 版本.最便携的是在代码中始终使用英文公式.
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屋！
