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:
< 0	The same random number on each call, using [Number] as the seed.
= 0	The 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 Double

								Dim rand2 As Double

								Dim rand3 As Double

								' Initialize the random number generator.

								Randomize

								rand1 = 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 Double

								Dim rand2 As Double

								Dim rand3 As Double

								Dim 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 lowerbound and 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 Integer

Randomize
RndInt = 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 Integer
rand1 = 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 lowerbound and 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 Double

Randomize
RndDbl = 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 Double
rand1 = 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

Return to the VBA Functions Page
Return to the Excel VBA Tutorial Page