VBA Calculate (Cell, Range, Row, & Workbook)

HomeVBA TutorialVBA Calculate (Cell, Range, Row, & Workbook)

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.

calculate-cell-value-using-vba

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.

using-vba-calculate-method

And at the end of the code, you can use the following line of code to switch to the automatic.

Application.Calculation = xlAutomatic
switch-to-automatic-using-vba

You can use calculation in the following way.

calculation-method-vba-code
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.

Calculate

Use Calculate Method for a Sheet

Using the following way, you can re-calculate all the calculations for all the

ActiveSheet.Calculate
Sheets("Sheet1").Calculate

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.

Sheets("Sheet1").Range("A1:A10").Calculate
Sheets("Sheet1").Range("A1").Calculate

🠔 Back to Excel VBA