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)
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) .
5. Rnd VisualBasic function – Where to use?
The function can be used in: Excel 2003, Excel 2007, Excel 2010, Excel 2013, Excel 2016 , Excel 2019.
