How do you get spreadsheet data in Excel to recalculate itself from within VBA, without the kluge of just changing a cell value?
Add a comment
|
6 Answers
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.
4 Comments
GSerg
Actually, there are .Calculate() and .CalculateFull() methods for that, on different levels.
Lance Roberts
I've tested Calculate on a sheet, and it not all functions in a cell recalculate. You can also do it by switching the 'dirty' bit, but I found this to be the easiest solution.
drwatsoncode
Using the
EnableCalculation property is a nice idea, because it allows a single sheet to be recalculated on the Worksheet level. In contrast, Application.CalculateFull forces a full calculation of all data, not only in a single Worksheet or Workbook, but in all open workbooks! This could be major overkill, especially since the user could have several unrelated, potentially complicated, workbooks open.
Calculus Knight
I wrote an answer about refreshing the active workbook without doing so with the other open ones.
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
1 Comment
hoang tran
For me
Calculate does the job, EnableCalculation however doesn't workSometimes 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.
Comments
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.)
Comments
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.EnableCalculation approach partly did the trick, but didn't cover unused cells.
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
Comments
After a data connection update, some UDF's were not executing. Using a subroutine, I was trying to recalcuate a single column with:
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.
Application.Calculation = xlManual
DoEvents
For Each mycell In Sheets("mysheet").Range("D9:D750").Cells
mycell.Formula = mycell.Formula
Next
DoEvents
Application.Calculation = xlAutomatic
