How do you get Excel to refresh data on sheet from within VBA?

Question

How do you get spreadsheet data in Excel to recalculate itself from within VBA, without the kluge of just changing a cell value?

Answer 1

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 I don't want to try and test the .CalculateFull() method, so it may work.

Answer 2

This should do the trick...

'recalculate all open workbooks
Application.Calculate

'recalculate a specific worksheet
Worksheet(1).Calculate

' recalculate a specific range
Worksheet(1).Columns(1).Calculate

Answer 3

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.)