Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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

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

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

`Int ((upperbound - lowerbound + 1) * Rnd + lowerbound )`

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)

### 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 #### Al Arafat Siddique

Hello! This is Arafat. Here I'm researching Microsoft Excel. I did my graduation from Bangladesh University of Engineering and Technology(BUET). My interest in data science and machine learning allured me to play with data and find solutions to real-life problems. I want to explore this data-driven world and make innovative solutions.

We will be happy to hear your thoughts 