'Calculate' always in the Status Bar
|Smart Recalculation||Evaluation Circumstances||Calculation Process||Dependency Trees|
|Controlling Calculation||"CALCULATE" in Status Bar||Calculation Methods||Calculating from VBA|
|Volatile Functions & Actions||User-Defined Functions||Repetitive Calculation||Version Timing Comparison|
Status Bar Shows “CALCULATE”
There are six known conditions in which the status bar will show CALCULATE:
Dependency Tree Limits
There are two limits to the number of dependencies that Excel versions prior to Excel 2007 can track before it must do full calculations instead of recalculations.
After the workbook has passed these limits, Excel no longer attempts to recalculate only changed cells. Instead, it recalculates all cells at each calculation. These limits saves the substantial amount of memory required for the dependency trees. When this happens the status bar permanently shows “CALCULATE” for all open workbooks even after closing the main workbook, until you close and reopen Excel.
According to Dave Gainer's Excel 12 blog, these limits have been removed in Excel version 2007. If you find that in Excel 2007 recalculation is taking longer than full calculation, or the time taken to update the dependency tree at each change is too large, you can force each calculation to be a full one using Workbook.ForceFullCalculation from VBA.
I do not know of a way of counting the number of dependencies, so it is not easy to work out how to get below the limit, but:
Because there are other conditions that can cause "Calculate" to appear in the status bar, test for the other conditions::
For Microsoft's official view on the dependency limit see MSKB Article Q243495 "Calculate Message Remains in Status Bar If 65,536 Formula References"