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

# How to Use VBA Rnd in Excel (4 Methods)

The Rnd function in Excel VBA returns a Single containing a pseudo-random number. Rnd is the abbreviation of Random. The Rnd function returns a value less than 1 but greater than or equal to 0. We can use this function in VBA to generate a random number using a predefined range of values. In this tutorial, I will show you how to use this function in Excel VBA to produce a random number.

## 4 Easy Ways to Use VBA Rnd in Excel

We can use the Rnd function in VBA to generate a random number between 0 and 1, a random integer number, different random numbers for each cell in the range, and produce a random number between a lower and upper bound.  The image below shows how to use the Rnd function to generate a random number between zero and one. ### 1. Produce a Random Number Between Zero (0) and One (1) Using VBA Rnd

By default, the Rnd function generates a random number between zero(0) and one(1). We will produce such a random number following the below steps.

Step 1:

• First, we will select Visual Basic from the Developer tab. We can also press ALT+F11 to open it. • Now, click on the Insert button and select Module. Step 2:

• Write down the following code in the window that appears.
``````Sub Rnd_Number()
Dim R  As Double
R = Rnd()
MsgBox R
End Sub``````
• Finally, click on the Run button to execute the code. Step 3:

• If a window named Macro appears, just click on Run from that window. • Now, you will see a message or pop box appears with a random number between zero and one on it. Step 4:

• We can also populate a specific cell with a random number. To do that, you have to insert the below code in the VBA macro We will generate a random number in cell B4.
``````Sub Rnd_Number_in_Cell()
Range("B4") = Rnd()
End Sub``````
• We will then Run the code. • We will now see cell B4 is populated with a random number between 0 and 1. • If we run the script multiple times, we will find out that the Rnd function generates different random numbers each time we execute the script. But we can produce or generate the same random number for every execution of the script if we insert a negative number as the argument of the Rnd. So if we replace the above code with the below one, we will get the same random number each time.
``````Sub Rnd_Number_in_Cell()
Range("B4") = Rnd(-2)
End Sub``````

### 2. Generate Random Integer Number Using VBA Rnd

The above way to use the VBA Rnd function shows only to generate a random number between 0 and 1. But we need to produce random numbers that are whole or integer. We can do that also using the same VBA Rnd function.

Steps:

• Insert the below code in the VBA macro. We will generate a whole or integer random number in cell B4.
``````Sub Rnd_Whole_Number()
Range("B4") = CInt(1 + Rnd * 100)
End Sub``````
• Finally, click on the Run button to execute the code. • We will now see cell B4 is populated with a random integer or whole number between 1 and 100. CInt function will round the random number with the decimal point that is generated by the VBA Rnd. ### 3. Generate Different Integer Random Numbers for Each Cell in the Range Using VBA Rnd

In the above example, we use the VBA Rnd function for a single cell (B4). But if we use this function for a range of cells, we will find that it is returning the same number for each cell in the range. But we can solve this problem easily and produce a different random number for each cell in the range.

Step 1:

• Insert the below code in the VBA macro We will generate different random numbers for each cell in the range of 10 consecutive cells (B4:B13).
``````Sub Random_Number_in_Range()
Dim i As Long
i = 10
For i = 1 To i
ActiveCell.Value = Rnd()
ActiveCell.Offset(1, 0).Select
Next i
End Sub``````
• Finally, click on the Run button to execute the code. • We will now see range B4:B13 is populated with different random numbers. Step 2:

• We can also generate different integer or whole random numbers for each cell in our range. Insert the below code in the VBA macro We will generate different integers or the whole random number for each cell in the range of 10 consecutive cells (B4:B13).
``````Sub Random_Number_in_Range()
Dim i As Long
i = 10
For i = 1 To i
ActiveCell.Value = CInt(1 + Rnd * 100)
ActiveCell.Offset(1, 0).Select
Next i
End Sub``````
• Finally, click on the Run button to execute the code. • The difference between the two scripts is that we have only used the Rnd function for the first one. But for the second one, we have used it with the CInt function just like we have done in Method 2 to generate a random integer number for a single cell. We will now see range B4:B13 is populated with different random whole numbers between 1 and 100. ### 4. Produce a Random Number Between a Lower Bound and Upper Bound

We can also use the VBA Rnd function to generate random numbers between a lower and upper bound.

Steps:

• Insert the below code in the VBA macro. We will generate a whole or integer random number between 1 and 50 in cell B4.
``````Sub Rnd_Number_Between()
Randomize
Dim RandomNumber As Single
RandomNumber = Int((50 - 1) * Rnd)
Range("B4") = RandomNumber
End Sub
``````
• Finally, click on the Run button to execute the code. • We will now see cell B4 is populated with a random integer or whole number between 1 and 50. Int function will round the random number with a decimal point generated by the Rnd. Note: Before calling the VBA Rnd function, use the Randomize statement without an argument to initialize the random-number generator with a seed based on the system timer.

## Things to Remember

• The VBA Rnd function takes only one argument (Number). This argument is optional and it should be a Single or any numeric expression.
• If the optional argument of the VBA Rnd function Number is less than zero, then the Rnd function will generate the same number each time we execute the script using the Number as seed.
• In case of the number is greater than zero, then the VBA Rnd will generate the next number in the pseudo-random sequence.
• If the number is zero, then the function will generate the most recently generated number.
• But if no single or numeric expression is supplied as the number, then the function will generate the next number in the pseudo-random sequence.

## Conclusion  