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.

**Table of Contents**hide

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

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

**Similar Readings:**

**How to Use the VBA Environ Function (4 Examples)****Use VBA IsEmpty Function (5 Relevant Examples)****How to Use VBA IsNumeric Function (9 Examples)****Use VBA Right Function in Excel (6 Examples)****How to Use IsDate Function in VBA (3 Examples)**

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

** **

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

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

**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 (). This argument is optional and it should be a Single or any numeric expression.*Number* - If the optional argument of the
**VBA Rnd**functionis*Number***less than zero**, then the Rnd function will generate the same number each time we execute the script using theas*Number***seed**. - In case of the
is*number***greater than zero**, then the**VBA Rnd**will generate the next number in the**pseudo-random sequence**. - If the
is*number***zero**, then the**function**will generate the most recently generated number. - But if
**no single or numeric expression**is supplied as the**number****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!!!