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

Get FREE Advanced Excel Exercises with Solutions!

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

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

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.

## 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. Alif Bin Hussain

Hello! Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc and my program was Civil Engineering from Bangladesh University of Engineering & Technology (BUET). I am a fresh graduate with a great interest in research and development. I do my best to acquire new information and try to find out the most efficient solutions.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  