# The VBA Rnd Function

## Description

The VBA Rnd function generates a random number that is greater than or equal to 0 and is less than 1.

The syntax of the function is:

Rnd( [Number] )

Where [Number] is an optional numeric argument meaning:

Value of Number:Random Number Generated:
< 0The same random number on each call, using [Number] as the seed.
= 0The most recently generated random number
> 0
(or omitted)
The next random number in the sequence
Note: if you want a different set of random numbers each time you run your code, it is recommended that you use the Randomize statement to initialize the random number generator, before calling the Rnd function. This is shown in Examples 1, 3 and 4 below.

## VBA Rnd Function Examples

### Example 1 - Generate Three Different Random Numbers

In the following example VBA code, each call to the Rnd function generates a different random number between 0 and 1:

 ' Generate three different random numbers between 0 and 1.Dim rand1 As DoubleDim rand2 As DoubleDim rand3 As Double' Initialize the random number generator.Randomizerand1 = Rnd( )' The variable rand1 is now equal to 0.705547511577606.rand2 = Rnd( )' The variable rand2 is now equal to 0.533424019813538.rand3 = Rnd( )' The variable rand3 is now equal to 0.579518616199493.

In the above VBA code, the [Number] argument has been omitted from the calls to the Rnd function. Therefore, each call to the function generates a different random number.

Note that, as recommended, the Randomize statement has been used to initialise the random number generator before the calls the Rnd function. Therefore, the above code will produce different random numbers each time it is run.

### Example 2 - Use the [Number] Argument To Produce Repeated Random Numbers

The following example VBA code illustrates how the different values for the [Number] argument affect the random numbers that are returned from the Rnd function.

 ' Generate random numbers between 0 and 1.Dim rand1 As DoubleDim rand2 As DoubleDim rand3 As DoubleDim rand4 As Double' Generate a random number relating to the seed value -2.rand1 = Rnd( -2 )' The variable rand1 is now equal to 0.713325679302216.' Generate the next random number in the sequence.rand2 = Rnd( )' The variable rand2 is now equal to 0.662433266639709.' Generate the random number relating to the seed value -2 (again).rand3 = Rnd( -2 )' The variable rand3 is now equal to 0.713325679302216.' Produce the most recently generated random number.rand4 = Rnd( 0 )' The variable rand4 is now equal to 0.713325679302216.

Note that, in the above VBA code:

• Every time the supplied [Number] is equal to -2, the function returns the same random number (0.713325679302216 in the above example);
• When the [Number] argument is omitted (or is > 0), the function returns the next random number in the sequence (in this example, this is 0.662433266639709);
• When the supplied [Number] argument is equal to 0, the function returns the most recently generated random number (=0.713325679302216 in the example).

### Example 3 - Produce A Random Integer Between Two Bounds

If you want to produce a random Integer between two values lowerboundand upperbound, use the VBA Rnd and Int functions as follows:

Int( lowerbound + Rnd * ( upperbound - lowerbound + 1 ) )

A simple function that performs this is shown below:

 ' Generate a random integer between supplied lower and upper bounds.Function RndInt( lowerbound As Integer, upperbound As Integer ) As IntegerRandomizeRndInt = Int( lowerbound + Rnd( ) * ( upperbound - lowerbound + 1 ) )End Function

In order to generate a random number between -5 and 5, for example, you could call the above function as follows:

 ' Generate a random integer between -5 and +5.Dim rand1 As Integerrand1 = RndInt( -5, 5 )

Note that the Randomize statement has been used to initialise the random number generator in the above RndInt function. Therefore, this function will produce different random numbers each time the code is run.

### Example 4 - Produce A Random Decimal Between Two Bounds

If you want to produce a random decimal value between two values lowerboundand upperbound, use the following formula:

lowerbound + Rnd * ( upperbound - lowerbound )

A simple function that performs this is shown below:

 ' Generate a random decimal between supplied lower and upper bounds.Function RndDbl( lowerbound As Double, upperbound As Double ) As DoubleRandomizeRndDbl = lowerbound + Rnd( ) * ( upperbound - lowerbound )End Function

In order to generate a random decimal that is greater than or equal to -5.0 and less than +5.0, for example, you could call the above function as follows:

 ' Generate a random decimal value between -5.0 and +5.0.Dim rand1 As Doublerand1 = RndDbl( -5, 5 )

Again, note that the Randomize statement has been used to initialise the random number generator in the RndDbl function. Therefore, this function will produce different random numbers each time the code is run.

## VBA Rnd Function Error

If the [Number] argument that is supplied to the Rnd function cannot be interpreted as a numeric value, the function will return the error:

Run-time error '13': Type mismatch 