Formulas and calculations are the major part of Microsoft Excel. During these calculations, we often get inappropriate or false values despite giving the correct formulas. It may happen because of dirty cells and their dependents in the dataset. To solve this, we have to then force recalculation. In this article, we will learn how to force recalculation with VBA in Excel based on 3 common cases among the users.
Download Practice Workbook
Get the sample file to try the methods by adding your own values.
3 Common Cases to Force Recalculation with VBA in Excel
To explain the cases, we prepared a sample workbook with two worksheets. Each of them contains 5 types of Product names and their Quantities with Prices. We got the Total Quantity by multiplying each Quantity by its Price. Similarly, we got the Sales amount by multiplying the Total Quantity by the Price.
But when we change the values in the Quantity column, the Total Quantity and Sales values remain the same.
This is where we need to force recalculation in the dataset using Excel VBA codes. We will do it for specific cells, specific worksheets and the whole workbook. Let’s go through each of them.
1. Apply VBA to Force Recalculation on Selected Cells in Excel
In this first case, we will apply the VBA code to force recalculation among some selected cells. To do this, follow the steps below.
- First, select the Cell range E5:E9 as we will force recalculation only to these cells.
- Then, go to the Developer tab and select Visual Basic under the Code group.
- In the Microsoft Visual Basic for Applications window, select Module from the Insert tab.
- Afterward, insert this code on the blank page.
Public Sub ForceRecalcSelection()
If TypeName(Selection) = "Range" Then Selection.Calculate
End Sub
- Next, select Run Sub/UserForm in the Run tab or press F5 on your keyboard.
- Finally, you will successfully get the recalculated values in the selected cells.
Read More: How to Recalculate in Excel (3 Effective Methods)
Similar Readings
- How to Do Bookkeeping for Small Business in Excel (Step-by-Step)
- Make Games in Excel (with Easy Steps)
- How to Fix Formula in Excel (9 Easy Methods)
- Create Uncertainty Budget in Excel (with Easy Steps)
- How to Create a Leaderboard in Excel (with Easy Steps)
2. Insert VBA to Force Recalculation for Specific Worksheet in Excel
In this section, we will look into the scenario where we will force recalculation for a specific worksheet in the workbook. In this worksheet, we will apply the VBA code to correct the values of Total Quantity and Sales.
Follow the steps below to do the task.
- First, open Microsoft Visual Basic for Applications window from the Developer > Visual Basic command.
- Then, go to Insert > Module to open a new page.
- Now, insert this code in the blank page.
Public Sub RecalculateActiveSheet()
EnableCalculation = False
EnableCalculation = True
Calculate
End Sub
- Afterward, press F5 on your keyboard to run the code.
- As a result, you will see that all the cells that had false results are showing correct results now.
Read More: How to Create a Calculator Using Macros in Excel (with Easy Steps)
3. Push Recalculation in Whole Workbook with Excel VBA
In this last case, we will apply the VBA code to force recalculation in the entire workbook. As we described initially, our first worksheet is showing wrong values when we changed the Quantity.
We also faced the same in the second worksheet as well.
To solve this, follow the simple steps below.
- First, open the Visual Basic Editor dialogue box from Developer > Visual Basic command.
- Then, press Insert > Module.
- Now, insert this code on the new page.
Sub Recalc_WholeWorkbook()
Dim wks As Worksheet
For Each wks In Worksheets
wks.Calculate
Next wks
End Sub
- Lastly, run the code by pressing F5 on your keyboard.
- Finally, you will see the first dataset recalculated its formulas and showed the correct values.
- Along with it, the second worksheet is also done with the recalculation successfully.
Read More: How to Make a Recalculate Button in Excel (with Easy Steps)
Additional Tips
- If you still face false values after applying the VBA code, go to the Formulas > Calculation Options section and mark checked the Automatic option.
- If you need to force recalculation several times in some specific worksheets or selected cells of different worksheets, then you do not need to type the code and run each time. Rather go to Developer > Macros and simply run the code without typing it.
Conclusion
That’s all about today’s article. Here we tried to demonstrate to you how to force recalculation with VBA in excel through 3 common scenarios that excel users face. Try them inserting your own values. Let us know your feedback in the comment box. Follow ExcelDemy for more articles like this.
Related Articles
- How to Calculate Cost of Funds in Excel (with Easy Steps)
- Make a Calculator in Excel (with Easy Steps)
- How to Calculate Root Mean Square Error in Excel
- Calculate APR in Excel (3 Simple Methods)
- How to Calculate WACC in Excel (with Easy Steps)
- Add Trailing Zeros in Excel (2 Easy Ways)
- How to Count Words in Excel (6 Easy Methods)
