Microsoft KB Archive/81856

{|
 * width="100%"|

Excel: Functions Recalculated If New Data or Cells Entered

 * }

-

The information in this article applies to:


 * Microsoft Excel for Windows, versions 2.x, 3.x, 4.x, 5.0, 5.0c
 * Microsoft Excel for Windows NT, version 5.0
 * Microsoft Excel for Windows, version 7.0
 * Microsoft Excel for the Macintosh, versions 2.20, 3.x, 4.x, 5.0

-

SUMMARY
In Microsoft Excel, when you use the AREAS, INDEX, OFFSET, CELL, INDIRECT, ROWS, COLUMNS, NOW, RAND, or TODAY functions, the formulas that use these functions are recalculated when either of the following occur:


 * You enter new data
 * You insert or delete cells, rows, or columns on the worksheet

This recalculation occurs regardless of whether cells are dependent on the new data.

WORKAROUNDS
To disable automatic recalculation, do either of the following:

 Choose Options from the Tools menu, select the Calculation tab and select the Manual option under Calculation (version 5.0) or choose Calculation from the Options menu and select the Manual option under Calculation.  Avoid using the following functions: AREAS

INDEX

OFFSET

CELL

INDIRECT

ROWS

COLUMNS

NOW

RAND 

MORE INFORMATION
This functionality can be compared to the VOLATILE macro function and the Visual Basic, Applications Editions Volatile method (Version 5.0 only) in Microsoft Excel. You can use the VOLATILE function, or the Volatile method in a user-defined function to cause the custom function to be recalculated under the same circumstances of data entry, insertions, and deletions.

Example for Microsoft Excel version 5.0
  In a new module, enter the following:      Function FeetPerSecond(MilesPerHour as Double) as Double Application.Volatile FeetPerSecond=MilesPerHour*5280/3600 End Function  On a worksheet, select the range A1:C50. In the Formula bar, type the formula =feetpersecond(60).

NOTE: This value must be entered as an array formula. To enter a formula as an array formula in Microsoft Excel for Windows or Microsoft Excel for OS/2, press CTRL+SHIFT+ENTER. In Microsoft Excel for the Macintosh, press COMMAND+ENTER. Enter any number into cell D1 and note that the worksheet is recalculated.</ol>

Because this user-defined function contains the Volatile method, this function is recalculated every time you enter data or insert or delete cells, columns or rows into any open worksheet. If the FeetPerSecond user- defined function did not contain the Volatile method, entering data into a cell would not cause a recalculation to occur.

Example for Microsoft Excel versions 3.0 and 4.0
<ol>  Enter the following into a macro sheet: <pre class="FIXEDTEXT">  A1:   FeetPerSecond A2:  =ARGUMENT(&quot;MilesPerHour&quot;,1) A3:  =VOLATILE A4:  =RETURN(MilesPerHour*5280/3600) </li> Select cell A1 and choose Define Name from the Formula menu. Select the Function option in the Macro section and choose OK.</li> Open a new worksheet and select cells A1:C50.</li> Choose Paste Function from the Formula menu. Scroll to the end of the listed functions. Select MACRO1!FeetPerSecond(MilesPerHour) and choose the OK button.</li> In the formula bar, replace MilesPerHour with 60.

NOTE: This value must be entered as an array formula. To enter a formula as an array formula in Microsoft Excel for Windows or Microsoft Excel for OS/2, press CTRL+SHIFT+ENTER. In Microsoft Excel for the Macintosh, press COMMAND+ENTER.

Microsoft Excel will return the value 88 in cells A1:C50.</li> Enter any number into cell D1 and note that the worksheet is recalculated.</li></ol>

Because this user-defined function has been defined with the VOLATILE function, this function will be recalculated every time you enter data or insert or delete cells, columns or rows into any open worksheet. If the FeetPerSecond user-defined function did not contain the VOLATILE function, entering data into a cell would not cause a recalculation to occur.

The functions listed above can be thought of as containing this VOLATILE function or method.

NOTE: In versions of Microsoft Excel earlier than version 3.0, the function OFFSET was available as a macro function only, not as a worksheet function. The macro function VOLATILE is not available in versions of Microsoft Excel earlier than version 3.0.