How to Generate Random Number with Excel VBA (4 Examples)

By using the built-in Rnd function in VBA Excel, we can generate a random number easily. These generated random numbers are usually in the range of 0 to 1. But we can set the lower-bound and upper-bound of the random numbers which is very helpful to create a dataset for various purposes.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Introduction to the RND Function

In Excel VBA, we use the Rnd function to generate random numbers just like the RAND function in Excel.
Objective: To generate a random number.
Syntax: Rnd [(Number)]
The only argument is an optional one. It has to be of Single Datatype or a valid Numeric Expression.

There are several cases,
Number < 0 – the Rnd function generates the same number every time.
Number = 0 – the Rnd function generates the most recently created number.
And Number > 0 – the Rnd function generates the next number of the random sequence.
If the Number argument is not supplied, the function generates the next number of the pseudo-random sequence.

The Randomize Statement
We need to use the Randomize statement to initiate the Rnd function. If we leave it without argument, it’ll use the system timer as the provided seed.

4 Examples to Generate Random Number with Excel VBA

How to write code in the Visual Basic Editor

Steps:

• Go to the Developer tab in the Excel Ribbon. It’ll open the Visual Basic Editor.

• In the Visual Basic Editor, click the Insert tab and choose the Module It’ll open a new module to write your code.

1. Generate Random Numbers in Between 0 to 1 in Excel VBA

In the following screenshot, we illustrated some random numbers generated by the Rnd function in Excel VBA.

The VBA code to generate these random numbers is given below.

Sub RandomNumber()
Randomize 'Initialize the Rnd function
'number < 0
Range("B5") = Rnd(-1)
Range("B6") = Rnd(-1)
Range("B7") = Rnd(-1)
'number > 0
Range("C5") = Rnd(1)
Range("C6") = Rnd(1)
Range("C7") = Rnd(1)
'number = 0
Range("D5") = Rnd(0)
'nummber argument is not given
Range("E5") = Rnd
Range("E6") = Rnd
Range("E7") = Rnd
End Sub

Copy this code to your Visual Basic Editor and press F5 to run it.

Explanation:
Cells-
B5:B7:  3 same random numbers in between 0 to 1 as the number argument is less than 0.
C5:C7:  3 different random numbers in between 0 to 1 as the number argument is greater than 0.
D5       :  Generated the most recently created number as the number argument is 0.
E5:E7:  3 different random numbers in the pseudo-random sequence.

In the code, we also initiated the Randomize statement before using the Rnd function.

Read More: Generate Random Number Between 0 and 1 in Excel (2 Methods)

2. Excel VBA to Generate a Random Whole Number

The CInt function in VBA converts a number to an integer data type. With the help of this function, we can generate random whole numbers by using the Rnd function.

2.1 Whole Number Between 0 and an Upper Bound

In this example, we can see that the random number ranges from 0 to a specified upper bound. Here cells B5:B7, show three random numbers from 0 to 20, and cells C5:C7 show another three random numbers from 0 to 100.

The code to generate these numbers is here below.

Sub RandomNumber()
Randomize 'Initialize the Rnd function
Range("B5") = CInt(Rnd * 20)
Range("B6") = CInt(Rnd * 20)
Range("B7") = CInt(Rnd * 20)
Range("C5") = CInt(Rnd * 100)
Range("C6") = CInt(Rnd * 100)
Range("C7") = CInt(Rnd * 100)
End Sub

2.2 Whole Number Between a Lower Bound and an Upper Bound

If we want to set the lower bound for generating random numbers along with the upper bound, we can try the following formula described in Microsoft’s Documentation.

In cells B5:B7, we generated 3 whole random numbers between 15 and 20. There are 3 more whole random numbers in the range of 50 to 60 in cells C5:C7.

Copy and then paste the following code in the Visual Basic Editor.

Sub RandomNumber()
Randomize 'Initialize the Rnd function
'Random Whole Numbers Between 15 and 20
Range("B5") = Int((20 - 15 + 1) * Rnd + 15)
Range("B6") = Int((20 - 15 + 1) * Rnd + 15)
Range("B7") = Int((20 - 15 + 1) * Rnd + 15)
'Random Whole Numbers Between 50 and 60
Range("C5") = Int((60 - 50 + 1) * Rnd + 50)
Range("C6") = Int((60 - 50 + 1) * Rnd + 50)
Range("C7") = Int((60 - 50 + 1) * Rnd + 50)
End Sub

Read More: Generate Random Number from List in Excel (4 Ways)

Similar Readings

• Auto Generate Invoice Number in Excel (with 4 Quick Steps)
• Random Number Generator with Data Analysis Tool and Functions in Excel
• How to Generate Random Data in Excel (9 Easy Methods)
• Random 5 Digit Number Generator in Excel (7 Examples)
• Random 4 Digit Number Generator in Excel (8 Examples)

3. Create a Random Decimal Number in Excel VBA

In this illustration, want to generate some decimal numbers having set their upper and lower bound. For this, we just need to remove the CInt function from the code. In the following screenshot, we can see a bunch of decimal random numbers with their lower and upper bound.

Here is the code.

Sub RandomNumber()
Randomize 'Initialize the Rnd function
'Random Decimal Numbers Between 15 and 20
Range("B5") = ((20 - 15 + 1) * Rnd + 15)
Range("B6") = ((20 - 15 + 1) * Rnd + 15)
Range("B7") = ((20 - 15 + 1) * Rnd + 15)
'Random Decimal Numbers Between 50 and 60
Range("C5") = ((60 - 50 + 1) * Rnd + 50)
Range("C6") = ((60 - 50 + 1) * Rnd + 50)
Range("C7") = ((60 - 50 + 1) * Rnd + 50)
End Sub

Read More: Generate Random Number in Excel with Decimals (3 Methods)

4. Add a Range of Unique Random Numbers Using VBA in Excel

The For…Next statement of MS Excel allows us to run a loop through a range of cells to show outputs. In this example, we dynamically selected a range of cells to output whole random numbers by using the Rnd function inside the For…Next loop.

4.1 Range of Unique Whole Numbers

In this example, we generated 10 random whole numbers between 50 to 60.

Run the code inside the Visual Basic Editor.

Sub RandomNumber()
Randomize 'Initialize the Rnd function
Dim i As Integer
i = 14
For i = 5 To i
    Cells(i, 2).Value = Int((60 - 50 + 1) * Rnd + 50)
Next i
End Sub

Code Explanation

• We took variable i as an integer and set its value to
• We executed the loop 10 times from i=5 to i=14.
• The Cells function sets the cell ranges from B5 to B14.
• We got 10 random unique whole numbers from 50 to 60 using the formula, Int((60 – 50 + 1) * Rnd + 50).

4.2 Range of Unique Decimal Numbers

In this example, we generated 10 random decimal numbers between 50 to 60.

Run the code inside the Visual Basic Editor.

Sub RandomNumber()
Randomize 'Initialize the Rnd function
Dim i As Integer
i = 14
For i = 5 To i
    Cells(i, 2).Value =((60 - 50 + 1) * Rnd + 50)
Next i
End Sub

Code Explanation

• We took variable i as an integer and set its value to
• We executed the loop 10 times from i=5 to i=14.
• The Cells function sets the cell ranges from B5 to B14.
• We got 10 random unique decimal numbers from 50 to 60 using the formula, ((60 – 50 + 1) * Rnd + 50).

Read More: How to Generate Random Number in a Range with Excel VBA

Notes

• Using the Randomize statement before calling the Rnd function in VBA code, is one of the recommendations from Microsoft’ Documentation.
• We can generate random numbers of different types using the RAND function easily in Excel without using VBA code.

Conclusion

Now, we know how to generate random numbers in Excel VBA. Hopefully, it would encourage these methods more confidently. Any questions or suggestions don’t forget to put them in the comment box below.

Related Articles

• How to Generate Random 10 Digit Number in Excel (6 Methods)
• How to Generate Random Numbers Without Duplicates in Excel (7 Ways)
• Excel VBA: Random Number Generator with No Duplicates (4 Examples)
• Generate Random Number with Mean and Standard Deviation in Excel
• Random Number Generator with Normal Distribution in Excel (4 Methods)