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

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.

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