Excel VBA Calculate Workbook (All/ Single/ Active Workbook)

Here’s an overview of how the entire workbook can be calculated with VBA and the dataset we’ll use to demonstrate various functions.

Excel Application.Calculate method

How to Calculate Workbooks Using Excel VBA

Example 1 – Calculate All Open Workbooks

We want the Total Price to be updated when we change the Quantity in the following dataset.

When the Quantities are changed, the Total Prices are still the same. To update the Total Price, we will use VBA code.

• Go to the Developer tab and select Visual Basic to open Microsoft Visual Basic for Applications.

• From the Insert tab, select Module.

• Insert the following code in the module.

Sub calculateallworkbooks()
Calculate
End Sub

• Click on the Run button to execute the code.

• Go to all the open workbooks and you will find that all the calculations are updated based on modified data.

Example 2 – Calculate the Active Worksheet

The Total Price column is not updated when the quantity column is changed.

• In the VBA code module, insert the following code to calculate only the active worksheet.

Sub activeworksheetcalculate()
ActiveSheet.Calculate
End Sub

• The Total Price column is updated.

Example 3 – VBA to Calculate a Specific Worksheet

• Use the following VBA code.

Sub specificsheetcalculate()
Sheets("Sheet 3").Calculate
End Sub

• Run the code to calculate the Total Price column of Sheet 3.

Example 4 – Calculate a Range Using VBA

• Copy the following code and paste it into the VBA module to calculate a specific range.

Sub calculaterange()
Sheets("Range").Range("E5:E9").Calculate
End Sub

• Press the Run button. The range E5:E9 will be calculated.

Read More: Excel VBA: Create a New Workbook and Name It

Example 5 – Calculate a Single Cell

• Use the code given below.

Sub calculatecell()
Sheets("cell").Range("E5").Calculate
End Sub

• The cell E5 will be re-calculated.

Example 6 – Calculate Selected Cells

• Select the cells you want to force-calculate.

• Apply the following VBA code in the code module and run it.

Sub calculateselectedcells()
Selection.Calculate
End Sub

• The selected cells will be updated.

Things to Remember

• You can keep the Calculation Options Automatic for automatic formula calculations.
• To get correct results, make sure the formulas are correct.

Frequently Asked Questions

What is Ctrl+Alt+F9 in VBA?

If you press Ctrl+Alt+F9, it will calculate all the formulas of each worksheet of all open workbooks.

Is VBA faster than formulas?

It is better to use formulas when the calculation is simple as it is faster. However, if the formula is really complex, it might be better to run a VBA.

How do I prevent automatic calculation in Excel?

Go to the Formulas tab and click on Calculation Options. From there select Manual. Now if you try to change any value in your worksheet, the formulas won’t be calculated automatically.

Download the Practice Workbook