Rnd VBA function - How to return a random number

Last Updated on January 11, 2023 by token

1. Rnd VBA function – Description

The Rnd VBA function returns a random number between 0 and 1. The function can be compared to the RAND function in an Excel sheet. We use this function to generate random numbers. The operation can be modified in any way to select a number from any range, ie. from 0 to 100, or from 500 to 1000. To do this, you should use the Rnd VBA function in conjunction with the VBA Round function.

The formula for generating an integer from any interval is as follows:

(interval start) + Round (Rnd * (interval_end - interval start), 0)

If you want to generate any floating point number in this way, you should apply the formula without the Round function, so:

(interval start) + Rnd * (interval_end - interval start)

An example of drawing any numbers is provided in point 3.

2. VBA Rnd function – Syntax

'OfficeInside.Org
Rnd([Number]) As Single

Number : Optional argument. You can use it to determine how the random number will be generated.

Number	What the function generates
-1	In subsequent draws, the function repeats the number previously drawn by the function.
1	Sequential random number in sequence.
0	The most frequently generated random number.
without indication	Sequential random number in sequence.

Function return : Single / Number

3. VBA Rnd function – Example

How to use Rnd function in VBA Excel? Below are examples of using the Rnd function in the VisualBasic Editor.

Example 1 : Randomizing a number in VBA between 0 and 1. The result is presented in the MsgBox window and sheet cells.

'OfficeInside.Org
MsgBox RndRange("A1") = Rnd

Example 2 : Randomizing a number in VBA between 0 and 100. The result is presented in the MsgBox window and sheet cells.

'OfficeInside.Org
MsgBox Round(Rnd * 100, 0)
Range("A2") = Round(Rnd * 100, 0)

Example 3 : Randomizing a number in VBA between 500 and 1000. The result is presented in the MsgBox window and sheet cells. In this example, number from 500 to 1000.

'OfficeInside.Org
MsgBox 500 + Round(Rnd * 500, 0)
Range("A2") = 500 + Round(Rnd * 500, 0)

Funkcje VBA Excel – Funkcja Rnd VBA — Excel VBA functions – Rnd VBA function

4. VB Rnd function – Additional information

The function requires no arguments or the use of parentheses.
With the help of functions, you can generate any integer or floating point number.
The formula for generating an integer number from any interval is as follows: (interval start) + Round (Rnd * (interval_end – interval start), 0).
If we want to generate any decimal number in this way, we should apply the formula without the Round function, so: (beginning of the interval) + Rnd * (end of the interval – beginning of the interval) .