Rnd VBA function – How to return a random number

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 randomize a number. The operation can be modified in any way to select a number from any range, eg from 0 to 100, or from 500 to 1000. To do this, we 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 we want to generate any floating point number in this way, we 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

12‘OfficeInside.Org
Rnd([Number]) As Single

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

NumberWhat the function generates
-1In subsequent draws, the function repeats the number previously drawn by the function.
1Sequential random number in sequence.
0The most frequently generated random number.
without indicationSequential 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-1. The result is presented in the MsgBox window and sheet cells.

123‘OfficeInside.Org
MsgBox RndRange(“A1”) = Rnd

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

123‘OfficeInside.Org
MsgBox Round(Rnd * 100, 0)Range(“A2”) = Round(Rnd * 100, 0)

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

123‘OfficeInside.Org
MsgBox 500 + Round(Rnd * 500, 0)Range(“A2”) = 500 + Round(Rnd * 500, 0)
Excel VBA functions - Rnd VBA function
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, we can generate any integer or floating point number.
  • The formula for generating an integer 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.

Leave a comment

Your email address will not be published. Required fields are marked *

%d bloggers like this: