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.


Download Practice Workbook

Download this practice book to exercise the task while you are reading this article.


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.

VBA Rnd in Excel


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.

Produce a Random Number Between Zero (0) and One (1)

  • Now, click on the Insert button and select Module.

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.

Click on the Run Button to Execute the Code

Step 3:

  • If a window named Macro appears, just click on Run from that window.

VBA Rnd in Excel

  • Now, you will see a message or pop box appears with a random number between zero and one on it.

Produce a Random Number Between Zero (0) and One (1)

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.

Run the code

  • We will now see cell B4 is populated with a random number between 0 and 1.

Cell is Populated with 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.

Generate Random Integer Number Using VBA Rnd

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

Generate Random Integer Number

Read More: How to Use VBA Randomize Function in Excel (5 Examples)


Similar Readings:


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.

. Generate Different Integer Random Numbers for Each Cell in the Range Using VBA Rnd

  • We will now see range B4:B13 is populated with different random numbers.

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.

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.

VBA Rnd in Excel

Read More: How to Use VBA Int Function in Excel ( 3 Examples)


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.

. Produce a Random Number Between a Lower Bound and Upper Bound Using VBA Rnd

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

. Produce a Random Number Between a Lower Bound and Upper Bound

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.

Read More: How to Use LCase Function in VBA in Excel (With 4 Examples)


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

In this article, we have learned to use the VBA Rnd function in Excel to generate random numbers. I hope from now on you can use the VBA Rnd function easily to generate decimal or whole random numbers in Excel. If you have any queries or recommendations about this article, please do leave a comment below. Have a great day!!!


Related Articles

ASM Arman

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

Leave a reply

ExcelDemy
Logo