How formula calculations are performed in Excel 2002 or Excel 2003 (825012)

MORE INFORMATION

Excel Versions Earlier Than 2002

In versions of Excel earlier than Excel 2002, formula calculations are performed on a sheet by sheet basis, starting with Sheet1 of your Excel workbook. After the calculations are complete on Sheet1, Excel calculates the formulas on Sheet2 until all the formulas are calculated on all the sheets of your workbook.

This process works fine as long as the precedents and dependents are on the same sheet. If the precedents and dependents are on different sheets but cross sheet references, this may cause some issues. For example, if the formula in Sheet1!A1 is =Sheet3!A4+1, both Sheet1!A1 and Sheet3!A4 have to calculate. Start with Sheet1. Try to calculate A1, you may realize that you cannot because Sheet3!A4 has not calculated yet. Continue trying to calculate what you can on Sheet1. When you are finished with Sheet1, calculate Sheet2. Then calculate Sheet3, finally updating Sheet3!A4. At this point you have hit all the sheets. However, Sheet1!A1 has still not been calculated, so go back and start calculating Sheet1 again. Finally, Sheet1!A1 can calculate. You will loop over all the sheets trying to calculate an arbitrary number of times, dependent on how many cross-sheet dependencies there are and how they are laid out.

This process has been determined to be an inefficient method for calculating formulas in your workbook.

Excel Versions 2002 and Later

In Excel version 2002 and later, one big list of formula precedents is kept for calculations. That is, all the formulas that are contained in your workbook are placed in memory as one continuous list of formulas. When you enter a new formula and it is calculated fully for the first time, is it put in its correct place in the calculation chain.

Excel starts the task of calculating the formulas from the top of the list. If a precedent formula has not been calculated, Excel jumps down the list of formulas and calculates the dependent formula. Then Excel returns to the precedent formula and completes the calculation.

After a formula in the list is complete, Excel moves to the next formula and continues calculating formulas until all the formulas in the list are calculated.

With this new process of calculating formulas, Excel will only make one pass in your workbook when calculating your formulas.
Some other calculation changes to note:

• Reentrant calculation is now disallowed. You cannot make a call to the range.calculate method in Microsoft Visual Basic for Applications (VBA) if you are already in a calculation. Cell A1 contains a user-defined function, inside the user-defined function you have a range.calculate. You receive a run-time error.
• You cannot perform a Name.Add method or a Name.Delete method in a user-defined function.
• If you are using a SUMIF() function or COUNTIF() function where any of the criteria are more than 255 characters, throw a
  #VALUE
  error message immediately.
• If you copy a whole sheet where any of the cells contain more than 255 characters, dirty the whole sheet for calculation immediately.
• You cannot perform a range.calculate when you have multiple sheets selected. This will also throw a runtime error.
• In some cases, Excel may appear to calculate very slowly compared to Excel 2000 and earlier, particularly when a full recalculation is performed by using Ctrl+Alt+F9 or when the full recalculation is performed when you open a file from a previous version of Excel in Excel 2002 or in Excel 2003 for the first time.