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.

Total Price Updated

Excel Application.Calculate method

Syntax Function
Application.Calculate Calculates all open workbooks
ActiveSheet.Calculate Calculates active worksheet
Sheets(“Sheet 1”).Calculate Calculates sheet 1
Worksheets(1).Rows(1).Calculate Calculates row 1 of sheet 1
Selection.Calculate Calculates selected cells

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.

Sample Dataset

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

Total Price Not Changing

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

Open Microsoft VBA

  • Then from the Insert tab, select Module.

Insert Module

  • Insert the following code in the module.
Sub calculateallworkbooks()
End Sub

Code to calculate All Open Workbooks

  • Click on the Run button to execute the code.

Run the Code

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

Total Price Updated

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.

Total Price Not Updated

  • In the VBA code module, write the following code to calculate only the active worksheet.
Sub activeworksheetcalculate()
End Sub

VBA Code to Calculate Active Sheet

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

Total Price Calculated

3. VBA Calculate Specific Worksheet

  • To calculate a specific worksheet, use the following VBA code.
Sub specificsheetcalculate()
Sheets("Sheet 3").Calculate
End Sub

VBA Code to Calculate Specific Worksheet

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

Total Price Updated

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()
End Sub

VBA Code to Calculate Range

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

Range 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()
End Sub

VBA Code to Calculate a Single Cell

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

Single Cell Calculated

6. Calculate Selected Cells

  • Select the cells you want to force calculate.

Select cells

  • 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()
End Sub

VBA Code to Calculate selected Cells

  • As a result, the selected cells will be updated.

Selected Cells Calculated

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.


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.

Get FREE Advanced Excel Exercises with Solutions!


Alif Bin Hussain
Alif Bin Hussain

Alif Bin Hussain earned a BSc in Civil Engineering from Bangladesh University of Engineering and Technology. As an engineering graduate, he has a deep passion for research and innovation. He loves to play with Excel. In his role as an Excel & VBA Content Developer at ExcelDemy, he not only solves tricky problems but also shows enthusiasm and expertise in handling tough situations with finesse, emphasizing his dedication to delivering top-notch content. He is interested in C, C++,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF