Microsoft Office Tutorials and References

In Depth Information

**Function Examples**

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.

Application.Volatile True

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:

Function NonStaticRand()

‘ Returns a random number that

‘ changes with each calculation

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 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:

Monthly Sales

Commission Rate

0–$9,999

8.0%

$10,000–$19,999

10.5%

$20,000–$39,999

12.0%

$40,000+

14.0%