Excel – Getting Excel to refresh data on sheet from within VBA

excelvba

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

Best Solution

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.