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