VBA：更新后无法自动重新计算 Excel 公式——需要手动交互
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14361270/
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
VBA: cannot automatically recalculate Excel formula after updating it -- needs manual interaction
提问by ACEG
In VBA, I am updating the formula in a cell (which works ok), but automatic recalculation does not work:
在 VBA 中，我正在更新单元格中的公式（工作正常），但自动重新计算不起作用：
updated_formula = "=COUNT(Sheet1!A3:A" & nr_points & ")"
Cells(x, y).Formula = updated_formula
ActiveWorkbook.Save
Cells(x, y).Calculate
The formula simply counts the number of existing rows in another sheet. When I run the macro, the cell's value in the function textfield is correct, but in the cell itself I have "#NAME?"and I need to press ENTERin the function to recalculate the formula.
该公式只是计算另一个工作表中现有行的数量。当我运行宏时，函数文本字段中单元格的值是正确的，但在单元格本身中我有"#NAME?"，我需要按下ENTER函数来重新计算公式。
Am I expecting too much of Excel? Or am I doing something wrong?
我是否对 Excel 期望过高？还是我做错了什么？
EDIT: Screenshot of the situation -- this is what I see after running the macro. (Sorry for the black censoring, have to maintain anonimity for the client company)
编辑：情况的屏幕截图——这是我在运行宏后看到的。（抱歉黑，必须为客户公司保密）
回答by Siddharth Rout
After seeing your screenshot, it became clear.
看了你的截图就明白了。
Change
改变
Cells(x, y).Formula = updated_formula
Cells(x, y).Formula = updated_formula
to
到
Cells(x, y).FormulaLocal = updated_formula
Cells(x, y).FormulaLocal = updated_formula
回答by Peter L.
Your code works fine on my sheet and updates calculated value immediately after macro run. Please make sure Calculation Optionsare set to "Automatic": Ribbon Datatab > Calculation Options > Automatic.
您的代码在我的工作表上运行良好，并在宏运行后立即更新计算值。请确保计算选项设置为“自动”：功能区数据选项卡 > 计算选项 > 自动。
回答by Gerhard Powell
I know this is a late post, but I had a similar issue and what worked for me was to change
我知道这是一篇迟到的帖子，但我遇到了类似的问题，对我有用的是改变
Cells(x, y).Calculate
to
到
Cells(x, y).Dirty
