How to Force Recalculation with VBA in Excel

Sometimes while executing formulas, incorrect values are returned despite the formulas being correct. This may happen as a result of dirty cells and their dependents in the dataset. In this article, we will discuss how to force recalculation with VBA in Excel to solve this issue, in 3 separate cases.

To illustrate the cases, we prepared a sample workbook with two worksheets. Each contains 5 types of Product names and their Quantities with Prices. We calculated the Total Quantity by multiplying each Quantity by its Price. Similarly, we calculated 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 a situation 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.

Case 1 – Forcing Recalculation on Selected Cells

Steps:

Select E5:E9, the range where we will force recalculation.

Go to the Developer tab and select Visual Basic under the Code group.

In the Microsoft Visual Basic for Applications window that opens, select Module from the Insert tab.

Enter this code in the Module window:

Public Sub ForceRecalcSelection()
    If TypeName(Selection) = "Range" Then Selection.Calculate
End Sub

Select Run Sub/UserForm in the Run tab, or press F5 on your keyboard.

The recalculated values appear in the selected cells.

Read More: How to Recalculate in Excel

Method 2 – Forcing Recalculation in a Specific Worksheet

Now let’s force recalculation on an entire worksheet. We will apply VBA code to correct the values of Total Quantity and Sales.

Steps:

Open the Microsoft Visual Basic for Applications window from the Developer > Visual Basic command.
Go to Insert > Module to open a new page.
Enter the following code;

Public Sub RecalculateActiveSheet()
  EnableCalculation = False
  EnableCalculation = True
  Calculate
End Sub

Press F5 to run the code.

All the cells that had false results are showing correct results now.

Case 3 – Forcing Recalculation in the Whole Workbook

In this last case, we will apply the VBA code to force recalculation in an entire workbook. As described initially, our first worksheet is showing incorrect values when we change the Quantity.

We faced the same issue in the second worksheet.

Let’s solve this issue in both sheets simultaneously.

Steps:

Open the Visual Basic Editor dialog box from Developer > Visual Basic command.
Click Insert > Module.
Enter this code in the module:

Sub Recalc_WholeWorkbook()
    Dim wks As Worksheet
    For Each wks In Worksheets
        wks.Calculate
    Next wks
End Sub

Run the code by pressing F5.

The first dataset has recalculated its formulas and is showing the correct values.

As is the second.

Additional Tips

If you still face false values after applying the VBA code, go to the Formulas > Calculation Options section and mark/check the Automatic option.

If you need to force recalculation several times in some specific worksheets or selected cells of different worksheets, instead of typing the code and running it each time, go to Developer > Macros and run the code without having to type it.