Have you ever experienced that Excel does not calculate all formulas correctly? I guess we have all experienced extremely large Excel spreadsheets with a lot of formulas. Now and then Excel refuses to calculate all the cell values. I don’t really know why, but it happens more frequently than I would hope for. One way to solve this issue, is to force Excel to re-calculate everything using VBA.
The magic to ensure that everything is calculated is to use three VBA methods:
Calculates all open workbooks.
Forces a full calculation of the data in all open workbooks.
For all open workbooks, forces a full calculation of the data and rebuilds the dependencies. Dependencies are the formulas that depend on other cells.
You will find the links to the Microsoft user guides for the above methods in the “Related” section below.
Option Explicit ' Force Full Calculation Public Sub ForceFullCalculation() On Error Resume Next ' Normal calculation Call Application.Calculate ' Extended calculation Call Application.CalculateFull ' Full Calculation Call Application.CalculateFullRebuild End Sub
The code can be executed in the following way:
' Perform full calculation of the data and rebuilds the dependencies. Call ForceFullCalculation()
I usually add a Button or a Shape to the main sheet to allow the user to easily force a full calculation of the Workbook. Just keep in mind that the method above re-calculates all open Workbooks. You could consider replacing “Application” with “Application.ThisWorkbook” to recalculate only the current Workbook, i.e. where you have added the custom button.
To add a custom button you need to
- Enable the Developer ribbon.
- Open the Visual Basic editor (Developer > Visual Basic).
- Double-click on Sheet1 and paste the code above, i.e. the full code of “Public Sub ForceFullCalculation()”.
- Press Developer > Controls > Insert > Form Controls > Button
- In the “Assign Macro” dialog, create a new click event handler, or select the method “Sheet1.ForceFullCalculation”.
In this case you could also have used a ActiveX Command Button. Then you would not have assigned a macro, but instead you would have added “Call ForceFullCalculation()” in the Click event.
If you want a fancier button, you can use a custom shape instead. Follow step 1 to 3 above. Instead of adding a button, press Insert > Shapes and select the shape you want. When added, right-click the shape, and select “Assign Macro…“. Then follow step 5 above to assign the ForceFullCalculation macro to the Click action.
One thought on “Excel VBA: Force full Calculation of Formulas”