Excel VBA 计算方法不更新数组公式；显示为#N/A
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9163877/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA Calculate Method not update Array Formulas; showing as #N/A
提问by acee
I have a series of array formulas in Excel that key off of each other. These are automatically resized to fit a range of data that is generated via a proprietary Excel add-in.
我在 Excel 中有一系列相互关联的数组公式。它们会自动调整大小以适应通过专有 Excel 加载项生成的一系列数据。
However, whenever my code rewrites some of the array formulas to the correct new size, the cells all show as #N/A until either you edit another unrelated cell on the sheet, save the sheet, or press F9.
但是，每当我的代码将某些数组公式重写为正确的新大小时，单元格都会显示为 #N/A，直到您编辑工作表上的另一个不相关单元格、保存工作表或按 F9。
Using code such as Application.Calculate, ActiveSheet.Calculate, etc do not have any effect.
使用 Application.Calculate、ActiveSheet.Calculate 等代码没有任何效果。
However, using SendKeys "{F9}" does.
但是，使用 SendKeys "{F9}" 确实如此。
As an example, these are two formulas on the sheet:
例如，这些是工作表上的两个公式：
={IF(LEN(INDEX(A:A, ROW()))>0,ROW(A:A)+2)}
and
和
={LARGE(OFFSET($J,0,0,ROW()),1)}
The first formula works fine after writing it programmatically to a range of cells. It merely tells me the row number of a cell that has text in it.
第一个公式在以编程方式写入一系列单元格后工作正常。它只是告诉我包含文本的单元格的行号。
The second formula does not work after writing it programmatically to a range of cells. It gives me the largest row number that has been previously seen in a list of numbers (which is the output of the first formula). If I press F9, the second formula updates correctly. If I do Application.Calculate in VBA, nothing happens. I've also tried the various other recalculate methods available at the Worksheet level as well, but no luck.
第二个公式在以编程方式写入一系列单元格后不起作用。它给了我以前在数字列表中看到的最大行号（这是第一个公式的输出）。如果我按 F9，第二个公式会正确更新。如果我在 VBA 中执行 Application.Calculate，则什么也不会发生。我还尝试了在工作表级别可用的各种其他重新计算方法，但没有运气。
Has anyone encountered something like this before?
有没有人遇到过这样的事情？
edit:The resize code essentially boils down to something like this (stripping out all of the support code that allows me to make more generalized calls to it):
编辑：调整大小的代码基本上归结为这样的事情（剥离所有支持代码，使我可以对其进行更通用的调用）：
First, I do:
首先，我这样做：
formula = dataSheet.Cells(startRow, startColumn).formula
Then later:
后来：
Set DeleteRange = dataSheet.Range(dataSheet.Cells(startRow, startColumn), dataSheet.Cells(bottomBound, rightBound))
DeleteRange.ClearContents
Set DeleteRange = Nothing
Then later on:
然后后来：
Set resultRange = dataSheet.Range(dataSheet.Cells(startRow, startColumn), dataSheet.Cells(startRow + Height - 1, startColumn + Width - 1))
resultRange.FormulaArray = formula
Set resultRange = Nothing
In a nut shell, I make a copy of the formula, clear the range, then rewrite it.
简而言之，我复制公式，清除范围，然后重写它。
回答by stevepastelan
If you can't beat 'em, join 'em.
如果你不能打败他们，就加入他们。
SendKeys "{F9}"
回答by brettdj
I have fleshed out my comment above given you have implemented this approach
鉴于您已经实施了这种方法，我已经充实了我上面的评论
using this code
使用此代码
Dim strFormula As String
strFormula = "=LARGE(OFFSET($J,0,0,ROW()),1)"
Range("a1:a5").FormulaArray = strFormula
- xl03 gives numbers but needs a calc to update the cells properly
- xl07 gives the "#N/A" (and raises a
Calculatein the statusbar) - xl10 works fine
- xl03 给出数字但需要计算才能正确更新单元格
- xl07 给出“#N/A”（并
Calculate在状态栏中引发 a ） - xl10 工作正常
As you point out none of the calculation options including a full dependency tree rebuild work
正如您指出的那样，没有任何计算选项包括完整的依赖树重建工作
using my RANDsuggestion above does force the update in xl07
使用我RAND上面的建议确实会强制更新 xl07
Dim strFormula As String
strFormula = "=LARGE(OFFSET($J,0,0,ROW()),1)+ RAND()*0"
Range("a1:a5").FormulaArray = strFormula
OFFSETis a volatile function see Voltatile Excel Functions(which includes a file that tests volatility)
OFFSET是一个易失性函数，请参阅Voltatile Excel 函数（其中包括一个测试波动性的文件）
Perhaps Charles Williams can shed some light on this, I will ping him
也许查尔斯威廉姆斯可以对此有所了解，我会打电话给他
回答by Charles Williams
Looks like a FormulaArray bug in 2003 and 2007.
A simpler bypass for your formula would be to use Range("a1:A5").Formula instead of formula array since =LARGE(OFFSET($J$1,0,0,ROW()),1) does not need to be an array formula
看起来像 2003 年和 2007 年的 FormulaArray 错误。
您的公式的一个更简单的绕过方法是使用 Range("a1:A5").Formula 而不是公式数组，因为 =LARGE(OFFSET($J$1,0,0,ROW( )),1) 不需要是数组公式
回答by KenB
My cells were not refreshing after ranges they depended on were modified either. I also had to hand edit each one to get them to re-calculate.
我的单元格在它们所依赖的范围被修改后也没有刷新。我还不得不手动编辑每一个，让他们重新计算。
SOLUTION:
解决方案：
Use fully qualified references within your formulas.
e.g. any formulas that look like this YourFunction(G200:H700)should be changed to look like this YourFunction('Your Sheet Name'!G200:H700).
在您的公式中使用完全限定的引用。例如，任何看起来像这样的公式YourFunction(G200:H700)都应该改变成这样YourFunction('Your Sheet Name'!G200:H700)。
Auto-Refresh now works perfectly for me.
自动刷新现在非常适合我。
回答by David M
My recollection is that there is also an Application.CalculateFullmethod - does that work?
我记得还有一种Application.CalculateFull方法 - 行得通吗？
