# How to Force Recalculation with VBA in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.

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. ### 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. • 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. ### 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. • 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 Sanjida Mehrun Guria

Hello! Welcome to my blog. I have completed my Bachelor in Architecture from Ashanullah University of Science & Technology, Dhaka. I am a passionate, goal-oriented person with an immense thirst for knowledge and an attitude to grow continuously. Besides Architectural work, I find it very enthusiastic to work in Excel blogging. Exceldemy is a platform where I have got the opportunity to flourish my skill in Microsoft Excel and therefore, here I will be posting blogs related to it.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  