Controlling Excel Calculation
|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|
Excel has a range of options allowing you to control the way it calculates. You can change these options using the Tools -->Options-->Calculation tab.
Calculation Settings Keep Changing
Because a number of Excel's calculation settings work at the application level (they are the same for all open workbooks), and are set by the first workbook opened, they may appear to change randomly depending on the sequence in which workbooks are opened. FastExcel Version 2 allows you to solve many of these problems.
Automatic calculation mode means that Excel will automatically recalculate
all open workbooks at each and every change, and whenever you open
Automatic except Tables.
Excel’s Data Tables feature is designed to do multiple calculations of the workbook, each driven by different values in the table. So using Automatic except Tables will stop Excel from automatically triggering the multiple calculations at each calculation, but will still calculate all dependent formulae except tables.
Application level settings.
The following calculation settings are held at the Application level rather than for each workbook, so they apply to all open workbooks.
Rather confusingly these application level settings are saved in each workbook, but only the settings in the first workbook opened are actioned: the settings in subsequent workbooks are ignored.
Workbook level settings.
The following calculation settings are held at workbook level, so each open workbook can have different settings.
All these workbook level calculation settings are saved and restored with the workbook, except Update remote references.
Controlling Excel’s Calculation Options.
FastExcel Version 2 significantly extends Excel’s calculation
options. See the FastExcel Version 2 User Guide for details.
Excluding Worksheets from Recalculation.
You can prevent worksheets from being calculated or recalculated
by using VBA to set the Worksheet.EnableCalculation property to False.
See Calculating from VBA.
FastExcel Version 2 gives you much better control over which worksheets are calculated using FastExcel MixMode settings.
Recalculate before Save
In Manual mode this checkbox controls whether Excel will recalculate the workbook as part of the save process. The default is Yes.
Update Remote References.
If TRUE Automatically update any remote references (DDE Links to other programs) whenever Excel recalculates.
Precision as Displayed.
Checking this box will force Excel to calculate to the number of
decimal places that appear as a result of your formatting, and will
permanently change any numbers stored in cells.
1904 Date System.
If TRUE changes the starting date from which all dates are calculated from January 1 1900 to January 2 1904.
Save External Link Values.
If TRUE Excel saves values for links to external workbooks. I recommend keeping this option as TRUE.
Accept Labels in Formulae.
This allows Excel to try and using “natural language” labels in formulae. Because there are circumstances where this will give you unexpected or ambiguous results, I recommend you do NOT use this feature.