Excel VBA Calculate Cell

In this article, we will learn how to use Excel VBA to calculate cell. We will calculate cell, workbook, worksheet, range, and formula using Excel VBA macro. Here we will also recalculate the formula if there is any error and set the calculating process once manually and then automatic way.

Calculating cells using Excel VBA is quite a useful process. If the calculating method is manual, then we cannot get the updated result of a formula if we can one of the values or any value. Sometimes we don’t notice if the calculating method is manual or automatic. So, this process checks and calculates if values are correct or not.

Download Practice Workbook

You may download the below workbook for practice.

How to Calculate Cell Using Excel VBA: 7 Ways

Therefore, we will calculate the workbook, worksheet, range, and formula here using Excel VBA macro. First, go to Developer >> Visual Basic, and Excel VBA will pop up. Then select Insert >> Module and the module window will pop up to write the code. This process is like all the methods here.

1. Calculating Workbook

• Initially, write the code and click on the run button to run the code.

Sub Cal_Workbook()
Dim ws As Worksheet
Dim rng As Range
For Each ws In ThisWorkbook.Worksheets
For Each rng In ws.UsedRange
rng.Calculate
Next rng
Next ws
MsgBox "Workbook has been recalculated!", vbInformation
End Sub

• In the final output, the workbook is recalculated and Msgbox is reassuring this.

2. Calculating Range

• Here we will write the code in the module window and click run.

Sub CalculateRange()
Set rng = Range("E16")
MsgBox "Current value of cell E16: " & rng.Value
rng.Calculate
MsgBox "Updated value of cell E16: " & rng.Value
End Sub

• Now first the output will show you the current value of cell E16.

• Finally, the output will show the correct value of cell E16.

3. Calculating Worksheet

• To calculate the worksheet write down the below code in the VBA module and click Run.

Sub CalculateWK()
Sheets("Worksheet").Calculate
MsgBox "Worksheet is Calculated"
End Sub

• The final output will be similar to below.

4. Calculating Formula

• To calculate the formula write down the code and click Run.

Sub CalculateFormula()
Set rng = Range("E16")
rng.Calculate
MsgBox "Formla is fine: " & rng.Value
End Sub

• Here, this program will calculate the formula and show the result in msg box as below.

5. Recalculating Not Working Formula

• Here we will recalculate the formula using Excel VBA if the formula is not working.

Sub Recalculate_Workbook()
Dim wkst As Worksheet   
 For Each wkst In Worksheets
 wkst.Calculate
Next wkst
MsgBox "This workbook is recalculated!", vbInformation
End Sub

• Once the program is complete the output will be similar to below.

6. Calculating Cell Manually with VBA

• Write down the code and Run to calculate the cells manually.

Sub Manually_Calculation()
Application.Calculation = xlManual
End Sub

• As the calculating method is selected manually, the total number is not changing and showing wrong.

7. Calculating Cell with VBA in Automatic Way

• In the beginning, the total number is showing wrong as the calculating method is “Manual”.

• Now, Run the code to automate the process.

Sub Automatic()
Application.Calculation = xlAutomatic
End Sub

• Finally, cell E16 is showing the correct output.

Things to Remember

• While using macro in Excel always save the macro in Excel Macro Enabled Workbook. Xlsm.
• The output is not visible unless there is any error or the calculating method is not manual. So, always check the calculating method below applying these VBA codes.

Frequently Asked Questions

Q1: How do you calculate cells in VBA?

Ans: To calculate cells in VBA you can use the codes already shown in this article. You will get different codes to calculate the cells in different ways.

Q2: What are cells () in VBA?

Ans: Cells in the VBA code show the reference of all cells in Excel.

Q3: How do I auto-calculate in Excel?

Ans: To auto-calculate in Excel go to Formulas Tab >> Calculator Options >> Automatic to auto-calculate in Excel.

Conclusion

Here, we learned how to Excel VBA calculate cell. Therefore, we will calculate cells, range, workbook, worksheet, and formula using Excel VBA. In this article, we also recalculated the formula if the formula is not working and checked if the calculating method is manual or automatic.  We cover every way to execute this process. Hopefully, you can solve the problem shown in this article. Please let us know in the comment section if there are any queries or suggestions, or you can also visit Exceldemy to explore more.