Microsoft Office Tutorials and References
In Depth Information
Controlling function recalculation
When you use a custom function in a worksheet formula, when is it recalculated?
Custom functions behave like Excel's built-in worksheet functions. Normally, a custom function is recalculated
only when it needs to be recalculated — that is, when you modify any of a function's arguments — but you can
force functions to recalculate more frequently. Adding the following statement to a Function procedure makes the
function recalculate whenever the workbook is recalculated:
The Volatile method of the Application object has one argument (either True or False). Marking a Function pro-
cedure as “volatile” forces the function to be calculated whenever calculation occurs in any cell in the worksheet.
For example, the custom STATICRAND function presented in this chapter can be changed to emulate the Excel
RAND() function by using the Volatile method, as follows:
Returns a random number that
changes when the sheet is recalculated
NONSTATICRAND = Rnd
Using the False argument of the Volatile method causes the function to be recalculated only when one or more of
its arguments change (if a function has no arguments, this method has no effect). By default, all custom functions
work as if they include an Application.Volatile False statement.
Selecting a cell at random
The following function, named DRAWONE, randomly chooses one cell from an input range and returns the
Function DRAWONE(rng As Variant) As Double
‘ Chooses one cell at random from a range
DRAWONE = rng(Int((rng.Count) * Rnd + 1))
If you use this function, you'll find that it is not recalculated when the worksheet is calculated. In other words,
the function is not a volatile function. (For more information about controlling recalculation, see the nearby
sidebar, “Controlling function recalculation.” You can make the function volatile by adding the following state-
After doing so, the DRAWONE function displays a new random cell value whenever the sheet is calculated.
A more general function, one that accepts array constants as well as ranges, is shown here: