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-
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 STATICRAND() As Double
‘ Returns a random number that doesn't
‘ change when recalculated
STATICRAND = Rnd
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-
STATICRANDBETWEEN = Int((hi – lo + 1) * Rnd + lo)
For example, if you want to generate a random integer between 1 and 1,000, you can use a formula such as