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.

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

Get FREE Advanced Excel Exercises with Solutions!