Excel Calculation Methods

Calculate Worksheet usually flags the Workbook as uncalculated in Manual mode.

In Manual Mode:

Recalculates only the uncalculated and volatile cells on the selected worksheet in dependency chain sequence, or if multiple worksheets are selected they are all calculated.
Note that this only gives you "correct" results if all precedent inter-sheet and inter-workbook dependencies have already been fully calculated, and that cells on other worksheets that are dependent on the sheet(s) are not recalculated.
After a sheet calculate the formulae in the sheet, the sheet and the workbook are flagged as uncalculated (CALCULATE shows in the statusbar), unless there were no uncalculated or volatile cells.

In Excel versions prior to 2308 Sheet Calculate does NOT reset dirtied/uncalculated cells as calculated after calculating them: the second of two successive Shift-F9 sheet recalculates in Manual mode will calculate exactly the same cells as the first.
In Excel version 2308 and later Sheet Calculate resets dirtied and uncalculated cells on the calculated worksheet(s) as calculated.

In Automatic Mode:

Recalculates all uncalculated and volatile cells on the selected worksheet, or if multiple worksheets are selected they are all calculated. If any cells were recalculated then flags the workbook as uncalculated, which triggers an automatic recalculation to recalculate any dependents on other sheets and all volatile cells and their dependents on ALL worksheets, not just the selected sheet(s). If there are no uncalculated dependents on other sheets or volatile cells then nothing happens in the triggered automatic calculation because the workbook was already calculated.

Calculate Range resets the calculation sequence for the selected range, and flags the calculated cells as uncalculated.

In Manual Mode:

Calculates all the cells in the selected range left-to-right and top-to-bottom. Excel 97 and Excel 2000 ignore dependencies within the selected range, but Excel 2002 and Excel 2003 follow the left-to-right and top-to-bottom calculation with a dependency recalculation of the selected range only.

This means that range.calculate can be significantly slower in Excel 2002 and 2003 than in Excel 97 and 2000, and will give different results if there are dependencies within the range being calculated that are not left-to-right and top-to-bottom.

Excel 2007 has two Range Calculate methods: Range.Calculate, which works the same way as Excel 2002/2003, and Range.CalculateRowMajorOrder, which works the same way as Excel97/2000.

Dependents and volatile cells which are outside the range are ignored. If one of the selected cells is part of a multi-cell array formula, then all the cells in the multi-cell array formula are calculated.

• If more than one area is selected, each area is calculated left-to-right and top-to-bottom, in the sequence that the areas were selected.
• If more than one Worksheet is selected then only the selected range on the active Worksheet is calculated.
• Conditional formats are NOT evaluated.
• After the cells have been calculated they are flagged as uncalculated.
• Iteration is ignored: the calculation is only done once regardless of the Iteration settings, unless calculation mode is Automatic in which case the calculation is done once follwed by an automatic calculation according to the Iteration settings.

Range.Calculate always calculates DATEVALUE using MMDDYY format, and does not calculate conditional formats.

Although Excel 2000 SR1 is documented as containing a fix for Range Calculate it still works the same way as above.

In Automatic Mode:

Range Calculate triggers an an automatic recalculation but does not itself explicitly calculate the cells referred to by the range.calculate. If there are no volatile cells then no calculations are done because the workbook was already calculated.
If there are any volatile cells then ALL the volatile cells and their dependents on ALL worksheets are recalculated.

Excel 2002/2003 Range Calculate Problems:

Range Calculate will fail in Excel 2002/2003 if any of these conditions is true:

• Calculation is set to Manual and Iteration is enabled.
• The range being calculated contains a complete circular reference chain of formulae (you get an error message with Excel 2002 SP3 and later)
• The range being calculated partially intersects a multi-cell array formula.
• The Range.Calculate or the Selection.Calculate Methods Fail After You Update Links to a Dynamic Data Exchange (DDE) Server.
  see MSKB 823338
• re-entrant calculation is not allowed: you cannot call Range.Calculate whilst a calculation is in progress
• range.calculate will fail if multiple sheets are selected

If the range you calculate includes a multi-cell array formula and you subsequently recalculate, the cells in the multi-cell array formula will be individually calculated.