Another function with no argument

I used to use Excel's RAND( ) function to quickly fill a range of cells with values. But I didn't like the fact that the random numbers change whenever the worksheet is recalculated. So I usually had to convert the formulas to values by choosing the Edit ^ Paste Special command (with the Values option).

Then I realized that I could create a custom function that returned random numbers that didn't change. I used the VBA built-in Rnd function, which returns a random number between 0 and 1. The custom function is as follows:

Function StaticRand()

' Returns a random number that doesn't ' change when recalculated

StaticRand = Rnd() End Function

If you want to generate a series of random integers between 0 and 1,000, you can use a formula such as this:

=INT(StaticRand()*1000)

The values produced by this formula never change, unlike those created by the built-in RAND( ) function.

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, as follows:

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, for example, will generate new random numbers for the StaticRand function presented in this chapter.

0 0

Post a comment