Application.Calculation = xlCalculationManual turns off automatic recalculation, and Application.Calculation = xlCalculationAutomatic turns it back on. Switch to manual at the start of a macro that writes to many cells, and back at the end, so Excel doesn’t recalculate after every change.
To recalculate while in manual mode, pick the scope. Application.Calculate recalculates all open workbooks (F9), Worksheets("Data").Calculate one sheet (Shift + F9), and Range("A1:D100").Calculate or Rows(5).Calculate only those cells; formulas outside the range that depend on them aren’t updated. Application.CalculateFull recalculates every formula, changed or not (Ctrl + Alt + F9).
The setting applies to the whole of Excel, not one workbook or sheet. Excel takes the mode from the first workbook opened in a session, so a file saved in manual can leave every file you open after it in manual too.
If the macro stops with an error between the two lines, Excel stays in manual and formulas stop updating without any warning. Restore the setting in an error handler:
Sub UpdateReport()
On Error GoTo CleanUp
Application.Calculation = xlCalculationManual
'your code here
CleanUp:
Application.Calculation = xlCalculationAutomatic
End SubBy default, in Excel, whenever you change a cell value Excel recalculates all the cells that have a calculation dependency on that cell. But when you are using VBA, you have an option to change it to the manual, just like we do in Excel.
Using VBA Calculate Method
You can change the calculation to the manual before you start a code, just like the following.
Application.Calculation = xlManualWhen you run this code, it changes the calculation to manual.
And at the end of the code, you can use the following line of code to switch to the automatic.
Application.Calculation = xlAutomatic
You can use calculation in the following way.
Sub myMacro()
Application.Calculation = xlManual
'your code goes here
Application.Calculation = xlAutomatic
End SubCalculate Now (All the Open Workbooks)
If you simply want to re-calculate all the open workbooks, you can use the “Calculate” method just like below.
CalculateUse Calculate Method for a Sheet
Using the following way, you can re-calculate all the calculations for all the
ActiveSheet.Calculate
Sheets("Sheet1").CalculateThe first line of code re-calculates for the active sheet and the second line does it for the “Sheet1” but you can change the sheet if you want.
Calculate for a Range or a Single Cell
In the same way, you can re-calculate all the calculations for a particular range or a single cell, just like the following.
Sheets("Sheet1").Range("A1:A10").Calculate
Sheets("Sheet1").Range("A1").CalculateRelated Tutorials
Written by
Puneet Gogia
Microsoft MVP — Excel · Founder, Excel Champs
Twelve years of teaching Excel, and a decade before that using it as a data analyst. Every tutorial here is written from a file I actually built.