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 95, version 7.0
- Microsoft Excel for the Macintosh, versions 2.2, 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
-or-
- 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:
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) quote (without
the quotation marks).
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.
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
- Enter the following into a macro sheet:
A1: FeetPerSecond
A2: =ARGUMENT("MilesPerHour",1)
A3: =VOLATILE()
A4: =RETURN(MilesPerHour*5280/3600)
- Select cell A1 and choose Define Name from the Formula menu. Select
the Function option in the Macro section and choose OK.
- Open a new worksheet and select cells A1:C50.
- Choose Paste Function from the Formula menu. Scroll to the end of
the listed functions. Select MACRO1!FeetPerSecond(MilesPerHour)
(without quotation marks) and choose the OK button.
- In the formula bar, replace MilesPerHour with "60" (without the
quotation marks).
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.
- Enter any number into cell D1 and note that the worksheet is
recalculated.
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.
REFERENCES
"Microsoft Excel Function Reference," version 4.0, page 451
"Microsoft Excel Function Reference," version 3.0, page 247
|