Excel 2007 Formulas (Mr. Spreadsheets Bookshelf)

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.

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.

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").