Getting Excel to refresh data on sheet from within VBA

VbaExcel

Vba Problem Overview

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

Vba Solutions

Solution 1 - Vba

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.

Solution 2 - 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

Solution 3 - Vba

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.

Solution 4 - Vba

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

Solution 5 - Vba

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

Solution 6 - Vba

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
Categories
Recommended Vba Solutions
Recommended Excel Solutions

Previous Solution:

In Django is there a way to display choices as checkboxes?

PythonDjango

Next Solution:

Python Dependency Injection Framework

PythonDependency Injection

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionLance RobertsView Question on Stackoverflow
Solution 1 - VbaLance RobertsView Answer on Stackoverflow
Solution 2 - VbaGrahamView Answer on Stackoverflow
Solution 3 - VbakambeeksView Answer on Stackoverflow
Solution 4 - VbaDave DuPlantisView Answer on Stackoverflow
Solution 5 - VbaAjV JsyView Answer on Stackoverflow
Solution 6 - VbapghcpaView Answer on Stackoverflow