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