Getting Excel to refresh data on sheet from within VBA
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
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.
You might also try
Application.CalculateFull
or
Application.CalculateFullRebuild
if you don't mind rebuilding all open workbooks, rather than just the active worksheet. (CalculateFullRebuild
rebuilds dependencies as well.)
Sometimes Excel will hiccup and needs a kick-start to reapply an equation. This happens in some cases when you are using custom formulas.
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.