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 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.

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 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) .

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: