Force Excel to Calculate Dependencies In Order
Overview
If you have ever used custom functions in Excel, depending on the complexity of them, you have probably run into an issue where the accuracy of the results was sporadic. There is a quick solution. Use CTRL ALT SHIFT F9.
The lengthier explanation from Microsoft explains that the calculation of worksheets in Excel can be viewed as a three-stage process:
- Construction of a dependency tree
- Construction of a calculation chain
- Recalculation of cell
With the introduction of complex VBA functions, the default calculation can produce inaccurate results because it doesn’t evaluate the dependency tree and calculation chain correctly.
So, if you have this issue, the most complete and thorough (and time consuming) calculation can be initiated by clicking CTRL ALT SHIFT F9. This forces the dependency tree to be rebuilt and recalculates the entire workbook. There are several levels in forcing Excel to calculate.
F9
Recalculates all cells that Excel has marked as dirty, that is, dependents of volatile or changed data, and cells programmatically marked as dirty. If the calculation mode is Automatic Except Tables, this calculates those tables that require updating and also all volatile functions and their dependents.
VBA: Application.Calculate
SHIFT F9
Recalculates the cells marked for calculation in the active worksheet only.
VBA: ActiveSheet.Calculate
CTRL ALT F9
Recalculates all cells in all open workbooks. If the calculation mode is Automatic Except Tables, it forces the tables to be recalculated.
VBA: Application.CalculateFull
CTRL ALT SHIFT F9
Causes Excel to rebuild the dependency tree and the calculation chain for a given workbook and forces a recalculation of all cells that contain formulas.
VBA: Workbooks(reference).ForceFullCalculation (introduced in Excel 2007)