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 — which is only when any of the function’s arguments are
modified. You can, however, force functions to recalculate more frequently. Adding the following
statement to a Function procedure makes the function recalculate whenever the sheet is
recalculated. If you’re using automatic calculation mode, a calculation occurs whenever any cell is changed.
The Volatile method of the Application object has one argument (either True or False ).
Marking a Function procedure as volatile forces the function to be calculated whenever
recalculation occurs for any cell in the worksheet.
For example, the custom StaticRand function can be changed to emulate Excel’s RAND
function using the Volatile method:
‘ Returns a random number that
‘ changes with each calculation
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 as a result of a recalculation. (If a function has
no arguments, this method has no effect.)
To force an entire recalculation, including nonvolatile custom functions, press Ctrl+Alt+F9. This
key combination will, for example, generate new random numbers for the StaticRand
function presented in this chapter.
The values produced by this formula never change when the worksheet is calculated normally.
However, you can force the formula to recalculate by pressing Ctrl+Alt+F9.
A function with one argument
This section describes a function for sales managers who need to calculate the commissions
earned by their sales forces. The calculations in this example are based on the following table: