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

Get FREE Advanced Excel Exercises with Solutions!

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.

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. Producing a Random Number Between Zero (0) and One (1) Using VBA Rnd Function

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 appear 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. Generating Random Integer Number Using VBA Rnd Function

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. Generating Different Integer Random Numbers for Each Cell in the Range Applying VBA Rnd Function

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

## Related Articles

ASM Arman

Hi there! I am ASM Arman. I Completed B.Sc. in Naval Architecture and Marine Engineering. I take a great interest in learning about new technologies and sharing my ideas and thoughts on them with others. Please do reach out to me if you have any queries or recommendations. Have a great day!!!

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF