Excel VBA Calculate Cell

Get FREE Advanced Excel Exercises with Solutions!

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.

Excel VBA calculate cell


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.

Dataset of Calculate VBA


1. Calculating Workbook

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

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

Write code to Excel VBA calculate cell

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

Final output after Excel VBA calculate cell


2. Calculating Range

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

Code

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

Write down code to Excel VBA calculate cell (range)

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

Output before Excel VBA calculate cell (range)

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

Output after Excel VBA calculate cell (range)


3. Calculating Worksheet

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

Code

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

Write down Excel VBA calculate cell (worksheet)

  • The final output will be similar to below.

Final output after calculating worksheet


4. Calculating Formula

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

Code

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

Write down code to calculate formula

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

Final output after calculating Formula


5. Recalculating Not Working Formula

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

Code

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

Write down code to recalculate

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

Final output after recalculating workbook


6. Calculating Cell Manually with VBA

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

Code

Sub Manually_Calculation()
Application.Calculation = xlManual
End Sub

Write down code to work manually

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

Final output after working manually


7. Calculating Cell with VBA in Automatic Way

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

Before applying code

  • Now, Run the code to automate the process.

Code

Sub Automatic()
Application.Calculation = xlAutomatic
End Sub

Write down code to work automatically

  • Finally, cell E16 is showing the correct output.

Final output after applying the method


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.

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Afrina Nafisa
Afrina Nafisa

Afrina Nafisa Alam, BSc, Industrial and Production Engineering from Ahsanullah University of Science and Technology. She has been working with the Exceldemy project for over 6 months and is currently a web content developer here. She has published over 18 articles and reviewed several during this period. She is keen to learn different features and deliver the knowledge in her current project. She is interested in learning different features of Microsoft Office, especially Excel, Power Query, Data Analysis,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo