Updating

A workbook can be updated (or calculated) when it is opened, closed.
You can also normally interrupt the calculation process by pressing Escape several times.
(F9) - [Application.Calculate] Calculates any cells "that have changed" in all open workbooks. This will only calculate formulas that have changed since the last calculation.
(Shift + F9) - [Sheets(1).Calculate] Recalculates all cells regardless of whether "they have changed" on the active worksheet.
(Ctrl + Alt + F9) - [Application.CalculateFull] Recalculates all cells regardless of whether "they have changed" in all open workbooks. This did not seem to work in Excel 2003 ?
There is currently no way to quickly recalculate all the cells in the active workbook.
A common shortcut key that is sometimes used is (Ctrl + Shift + F9) but this is not available by default.
(Ctrl + Alt + Shift + F9) - [Application.CalculateFullRebuild] Recalculates absolutely everything regardless in all open workbooks. This includes all custom worksheet functions and external worksheet functions. Available from Excel 2002.


Calculation

Automatic - (Application Setting) Calculates all dependent formulas every time you make a change to a value, formula or name. Any workbook that contains formulas will be automatically calculated when it is opened. This is the default calculation setting.
Automatic except tables - Calculates all dependent formulas except those in data tables. You can calculate the formulas in data tables by pressing the "Calc Now" button,
Manual - Calculates only when requested by the user. "Calculate" will appear in the status bar when you have uncalculated formulas.
Recalculate before save - Calculates all dependent formulas in the worksheet before it is saved.
Enable iteration calculation - This can be used to restrict the iteration when using circular references. The Maximum iterations and Maximum changes options should be disabled when this checkbox is not ticked. There is no iteration (circular references cannot be calculated) when this checkbox is cleared. The default is not ticked.
Maximum iterations - This is the maximum number of iterations used before iteration will stop. The default is 100.
Maximum change - The largest number that the change in value, must be less than, before iteration will stop. The smaller the number the more accurate the result. The default is 0.001.
All these settings are workbook specific but it is the first workbook that is opened that determines what the settings are. Opening subsequent workbooks will not change the options.


Automatic Calculation

The default setting is Automatic this means that Excel will automatically recalculate all the necessary cells in a workbook when a value is changed.
This type of calculation is the default and is what some people call a minimum recalculation.
This means that the data and formulas in your workbook are constantly up-to-date.
Most of the time when the workbook is fairly simple recalculation takes a fraction of a second since the majority of the cells will be unaffected.
When you have a complicated workbook containing a lot of formulas, Excel will determine which cells to recalculate and in what order.
It is important to remember though that Excel can only track 65,536 dependencies to unique references.
If Excel finds more than 65,536 dependencies then the whole workbook is calculated and the word "Calculate" may remain in the status bar. For more information please refer to this Knowledge Base Article (243495).


Manual calculation

This lets you select or clear the Recalculate Before Save check box.
The "Recalculate Before Save" check box controls if the formulas are recalculated before the workbook is closed.
This is often a good idea, as it prevents a user opening the workbook to display data that is not up-to-date.
Manual calculation is a good idea when you have large workbooks as you want to control when a recalculation occurs.
If your workbook contains any uncalculated formulas in the active workbook when you are working in Manual calculation the word "Calculate" will be displayed in the status bar.


 


Why does my Calculation change to Manual ?

Your calculation setting (and your iteration setting) is actually an application setting rather than a workbook setting.
However despite this being an application setting it is saved into your individual workbooks.
The calculation settings are taken from the first workbook you open and then ignored in subsequent workbooks.
Depending on the order in which you open your workbooks your settings may change.
For example lets say that all of your workbooks have been saved with a calculation settings of Automatic.
This works fine for you until by chance the first workbook you open in your session happens to be a workbook created by someone else (who by mistake has saved it with manual calculation).
In this scenario your calculation settings will be set to manual and when you open up your own workbooks the calculation will still be set to manual.


Range.Dirty ??

This is used to add the specified cells to the list of cells requiring calculation at the next recalculation



Important

For information about Iteration and Circular References, please refer to the Circular References page.
Calculation is not a workbook or worksheet specific setting. When you change this setting it will affect all open workbooks (and worksheets).


© 2017 Better Solutions Limited. All Rights Reserved. © 2017 Better Solutions Limited

Top

PrevNext