Microsoft Office Tutorials and References

In Depth Information

=STATICRANDBETWEEN(1,1000)

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:

Application.Volatile True

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:

Function NONSTATICRAND()

‘

Returns a random number that

‘

changes when the sheet is recalculated

Application.Volatile True

NONSTATICRAND = Rnd

End Function

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

cell's contents:

Function DRAWONE(rng As Variant) As Double

‘ Chooses one cell at random from a range

DRAWONE = rng(Int((rng.Count) * Rnd + 1))

End Function

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-

ment:

Application.Volatile True

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: