By 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 = xlManual
When 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 Sub
Calculate 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.
Use Calculate Method for a Sheet
Using the following way, you can re-calculate all the calculations for all the
The 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.
⇠ Back to What is VBA in Excel
Helpful Links – Developer Tab – Visual Basic Editor – Run a Macro – Personal Macro Workbook – Excel Macro Recorder