A recent post at Stack Overflow reminded me that I have not posted about the pros and cons of Excel’s Workbook.ForceFullCalculation property.
So what does Workbook.ForceFullCalculation do?
The simple answer is that it switches off Excel’s smart recalculation algorithm, so that every calculation recalculates every formula in all open workbooks.
Why is ForceFullCalculation Bad?
ForceFullCalculation slows down calculation because every formula is treated as though it is volatile and gets calculated, rather than only calculating the formulas that depend either on something that has changed since the last calculation or on a volatile function.
When is ForceFullCalculation Good?
When ForceFullCalculation is switched on Excel does not need to build and maintain the dependency trees. The dependency trees are created each time you open a workbook, and are maintained every time you make a change to a workbook.
- Your workbook takes a long time to open
- Deleting or changing formulas takes a long time
then switching on ForceFullCalculation could speed things up a lot.
Also the dependency trees can take up a lot of memory so eliminating them can be good.
So its a trade-off: faster workbook opening and editing, and less memory footprint versus slower calculation.
(And you almost certainly need to be using Manual Calculation mode.)
So how do you switch it on?
There are several ways of setting ForceFullCalculation:
- Click Alt-F11 to go to the Visual Basic Editor, select the workbook in the Project Explorer, Press F4 to see the workbook properties and change ForceFullCalculation to true.
- Add this VBA statement to a VBA sub and execute it.
- If you have FastExcel V3 installed then you can set it from the FastExcel Workbook Calculation Options page.
The Quirks of ForceFullCalculation
There are a few more things you should know about this property:
- Although it is a Workbook Property it works at Application level and so affects all open workbooks.
- It does get saved with the workbook and, if True, re-establishes itself when the workbook is reopened.
- It makes “Calculate” appear in the StatusBar, and you cannot make it go away with any of the F9 calculate commands.
- Once you have turned it on in an Excel session turning it off again does not restore Smart Recalculation: you have to close and restart Excel.
Trying ForceFullCalculation out
Here are the Jimwinz’s instructions from Stack Overflow for creating a demonstration of the problem:
- Create a new file (save and exit other Excel files!)
- To create the data to count, in A2 enter =RANDBETWEEN(0,10000) if you have a slow CPU or RANDBETWEEN(0,20000) if you have a faster CPU
- Copy A2 down to row A10k or A20k.
- Copy and paste values for column A
- Switch to Manual Calculation
- In B2, enter =COUNTIF($A$1:A2,A2), copy down and press F9 to calculate
- Once sheet is fully calculated, select all the CountIf cells in column B and use the Clear All function. This is the stage where Excel seems to freeze for a long time.
- Now click Undo to get the formulas back.
- Switch on ForceFullCalculation using one of the methods shown above. The statusbar should show Calculate and pressing F9 does not make it go away. Now Clear All is virtually instantaneous.
So what is your experience with this nifty workbook property?