Generating Random Numbers

This section presents two functions that deal with random numbers. One generates random numbers that don't change. The other selects a cell at random from a range.

Generating Random Numbers That Don't Change

You can use the Excel RAND function to quickly fill a range of cells with random values. But, as you may have discovered, the RAND function generates a new random number whenever the worksheet is recalculated. If you prefer to generate random numbers that don't change with each recalculation, use the following STATICRAND Function procedure:

 Function STATICRAND() As Double '   Returns a random number that doesn't '   change when recalculated     STATICRAND = Rnd End Function 

The STATICRAND function uses the VBA Rnd function, which, like Excel's RAND function, returns a random number between 0 and 1. When you use STATICRAND, however, the random numbers don't change when the sheet is calculated.

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 any cell changes:

 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 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 functions work as if they include an Application.Volatile False statement.

Note 

Pressing F9 does not generate new values from the STATICRAND function, but pressing Ctrl+Alt+F9 (Excel's "global recalc" key combination) does.

Following is another version of the function that returns a random integer within a specified range of values. This function is essentially a "wrapper" for Excel's RANDBETWEEN function.

 Function STATICRANDBETWEEN(lo As Long, hi As Long) As Long '   Returns a random integer that doesn't '   change when recalculated     STATICRANDBETWEEN = WorksheetFunction.RandBetween(lo, hi) End Function 

For example, if you want to generate a random integer between 1 and 1000, you can use a formula such as

 =STATICRANDBETWEEN(1,1000) 

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

 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:

 Function DRAWONE2(rng As Variant) As Variant '   Chooses one value at random from an array     Dim ArrayLen As Long     If TypeName(rng) = "Range" Then         DRAWONE2 = rng(Int((rng.Count) * Rnd + 1)).Value     Else         ArrayLen = UBound(rng) - LBound(rng) + 1         DRAWONE2 = rng(Int(ArrayLen * Rnd + 1))     End If End Function 

This function uses the VBA built-in TypeName function to determine whether the argument passed is a Range. If not, it's assumed to be an array. Following is a formula that uses the DRAWONE2 function. This formula returns a text string that corresponds to a suit in a deck of cards:

 =DRAWONE2({"Clubs","Hearts","Diamonds","Spades"}) 

Following is a formula that has the same result, written using Excel's built-in functions:

 =CHOOSE(RANDBETWEEN(1,3),"Clubs","Hearts","Diamonds","Spades") 

I present two additional functions that deal with randomization later in this chapter (see "Advanced Function Techniques").