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

Formulas and calculations are basic functions of Excel. Sometimes while working with formulas, they might give inaccurate results or calculations might not update even when the dataset is changed. To resolve all of these, you can use the Application.Calculate method in VBA to calculate a workbook in Excel.

In this article, we will explore Excel VBA calculate workbook. We will start the article by explaining how to calculate all open workbooks using VBA. Then we will move on to discuss steps to calculate active and specific worksheets, a single cell, and a range. We will wrap up by describing how to force calculate selected cells in Excel using VBA.

Excel Application.Calculate method

How to Calculate Workbooks Using Excel VBA

1. Calculate All Open Workbooks

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

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

• First of all, go to the Developer tab and select Visual Basic to open Microsoft Visual Basic for Applications.

• Then 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.

2. Calculate Active Worksheet

The Total Price Column is not updated when the quantity column is changed. We will use the following steps to update the calculations of the active worksheet.

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

Sub activeworksheetcalculate()
ActiveSheet.Calculate
End Sub

• As a result, the Total Price column is updated.

3. VBA Calculate Specific Worksheet

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

4. Calculate a Range of Formula 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. Consequently, the range E5:E9 will be calculated.

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

5. Calculate a Single Cell

• If you want to calculate only a single cell, you can use the code given below.

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

• As a result, cell E5 will be re-calculated.

6. Calculate Selected Cells

• Select the cells you want to force calculate.

• If the selected cells are not calculated automatically when the dataset is changed, apply the following VBA code in the code module and run it.

Sub calculateselectedcells()
Selection.Calculate
End Sub

• As a result, 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 right and then use the VBA code.

Frequently Asked Questions

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

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

3. 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 Practice Workbook

Download this practice workbook while reading this article.

Conclusion

Thanks for making it this far. I hope you found this article helpful. In this article, we have explained how to calculate a workbook using Excel VBA. We have provided VBA codes to calculate all open workbooks, active and specific worksheets, a range, a single cell, etc. We have also covered how to force calculate selected cells using VBA. If you have any queries or recommendations regarding this article, feel free to let us know in the comment section below.