How to Force Recalculation with VBA in Excel

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.

How to Force Recalculation with VBA in Excel: 3 Common Cases

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

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

3. Push Recalculation in the 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

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.

Download Practice Workbook

Get the sample file to try the methods by adding your own values.

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.

<<Go Back to Excel Calculation Options | How to Calculate in Excel | Learn Excel