Microsoft Office Tutorials and References
In Depth Information
Generating Random Numbers
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 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:
Function STATICRANDBETWEEN(lo As Long, hi As Long) As Long
‘ Returns a random integer that doesn’t
‘ change when recalculated
STATICRANDBETWEEN = Int((hi – lo + 1) * Rnd + lo)
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

Search JabSto ::

Custom Search