ExcelFunctions.net

Search Site:

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.

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( ) rand3 = Rnd( )' The variable rand2 is now equal to 0.533424019813538. ' 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.

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). ' Produce the most recently generated random number.rand3 = Rnd( -2 ) ' The variable rand3 is now equal to 0.713325679302216. 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).

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 IntegerRandomize End FunctionRndInt = Int( lowerbound + Rnd( ) * ( upperbound - lowerbound + 1 ) ) |

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.

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 DoubleRandomize End FunctionRndDbl = lowerbound + Rnd( ) * ( upperbound - lowerbound ) |

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.

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