让 Excel 从 VBA 中刷新工作表上的数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/154434/
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
Getting Excel to refresh data on sheet from within VBA
提问by Lance Roberts
How do you get spreadsheet data in Excel to recalculate itself from within VBA, without the kluge of just changing a cell value?
如何在 Excel 中获取电子表格数据以从 VBA 中重新计算自身，而无需更改单元格值？
回答by Lance Roberts
The following lines will do the trick:
以下几行可以解决问题：
ActiveSheet.EnableCalculation = False
ActiveSheet.EnableCalculation = True
Edit:The .Calculate()method will not work for all functions. I tested it on a sheet with add-in array functions. The production sheet I'm using is complex enough that I don't want to test the .CalculateFull()method, but it may work.
编辑：该.Calculate()方法不适用于所有功能。我在带有附加数组函数的工作表上对其进行了测试。我使用的生产表非常复杂，我不想测试该.CalculateFull()方法，但它可能有效。
回答by Graham
This should do the trick...
这应该可以解决问题...
'recalculate all open workbooks
Application.Calculate
'recalculate a specific worksheet
Worksheets(1).Calculate
' recalculate a specific range
Worksheets(1).Columns(1).Calculate
回答by kambeeks
Sometimes Excel will hiccup and needs a kick-start to reapply an equation. This happens in some cases when you are using custom formulas.
有时 Excel 会出现问题，需要启动才能重新应用方程式。当您使用自定义公式时，在某些情况下会发生这种情况。
Make sure that you have the following script
确保您有以下脚本
ActiveSheet.EnableCalculation = True
Reapply the equation of choice.
重新应用选择的方程式。
Cells(RowA,ColB).Formula = Cells(RowA,ColB).Formula
This can then be looped as needed.
然后可以根据需要循环。
回答by Dave DuPlantis
You might also try
你也可以试试
Application.CalculateFull
or
或者
Application.CalculateFullRebuild
if you don't mind rebuilding all open workbooks, rather than just the active worksheet. (CalculateFullRebuildrebuilds dependencies as well.)
如果您不介意重建所有打开的工作簿，而不仅仅是活动工作表。（也CalculateFullRebuild重建依赖项。）
回答by AjV Jsy
I had an issue with turning off a background image (a DRAFT watermark)in VBA. My change wasn't showing up (which was performed with the Sheets(1).PageSetup.CenterHeader = ""method) - so I needed a way to refresh. The ActiveSheet.EnableCalculationapproach partly did the trick, but didn't cover unused cells.
我在 VBA 中关闭背景图像（草稿水印）时遇到问题。我的更改没有显示出来（这是用Sheets(1).PageSetup.CenterHeader = ""方法执行的） - 所以我需要一种刷新的方法。这种ActiveSheet.EnableCalculation方法在一定程度上起到了作用，但没有覆盖未使用的单元格。
In the end I found what I needed with a one liner that made the image vanish when it was no longer set :-
最后，我找到了我需要的一个衬垫，当它不再设置时，它会使图像消失：-
Application.ScreenUpdating = True
Application.ScreenUpdating = True
回答by pghcpa
After a data connection update, some UDF's were not executing. Using a subroutine, I was trying to recalcuate a single column with:
数据连接更新后，某些 UDF 未执行。使用子例程，我试图重新计算单个列：
Sheets("mysheet").Columns("D").Calculate
But above statement had no effect. None of above solutions helped, except kambeeks suggestion to replace formulas worked and was fast if manual recalc turned on during update. Below code solved my problem, even if not exactly responsible to OP "kluge" comment, it provided a fast/reliable solution to force recalculation of user-specified cells.
但是上面的说法没有任何作用。上述解决方案都没有帮助，除了 kambeeks 建议替换公式有效并且如果在更新期间打开手动重新计算会很快。下面的代码解决了我的问题，即使不完全负责 OP“kluge”注释，它也提供了一个快速/可靠的解决方案来强制重新计算用户指定的单元格。
Application.Calculation = xlManual
DoEvents
For Each mycell In Sheets("mysheet").Range("D9:D750").Cells
mycell.Formula = mycell.Formula
Next
DoEvents
Application.Calculation = xlAutomatic
