Microsoft Office Tutorials and References

In Depth Information

Generating Random Numbers

This section presents functions that deal with random numbers. One generates random numbers that don't

change. The other selects a cell at random from a range.

The functions in this section are available at this topic's website. The filename is ran-

dom functions.xlsm.

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.

Pressing F9 does not generate new values from the STATICRAND function, but press-

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

Function STATICRANDBETWEEN(lo As Long, hi As Long) As Long

‘ Returns a random integer that doesn't change when recalcu-

lated

STATICRANDBETWEEN = Int((hi – lo + 1) * Rnd + lo)

End Function

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