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.

**Download Practice Workbook**

Download this practice workbook to exercise while you are reading this article.

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

**the Rnd function generates the**

*Number*= 0 â€“**most recently created number**.

*And*

**the Rnd function generates the**

*Number*> 0 â€“**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.**

**Read More:** **Generate Random Number Between 0 and 1 in Excel (2 Methods)**

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

**Similar Readings**

**Auto Generate Invoice Number in Excel (with 4 Quick Steps)****Random Number Generator with Data Analysis Tool and Functions in Excel****How to Generate Random Data in Excel (9 Easy Methods)****Random 5 Digit Number Generator in Excel (7 Examples)****Random 4 Digit Number Generator in Excel (8 Examples)**

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

**Notes**

- Using the
**Randomize statement**before calling the**Rnd function**in VBA code, is one of the**recommendations**from**Microsoftâ€™ Documentation**. - We can
**generate random numbers**of different types using the**RAND function**easily in Excel**without**using**VBA code.**

**Conclusion**

Now, we know how to generate random numbers in Excel VBA. Hopefully, it would encourage these methods more confidently. Any questions or suggestions donâ€™t forget to put them in the comment box below.

**Related Articles**

**How to Generate Random 10 Digit Number in Excel (6 Methods)****How to Generate Random Numbers Without Duplicates in Excel (7 Ways)****Excel VBA: Random Number Generator with No Duplicates (4 Examples)****Generate Random Number with Mean and Standard Deviation in Excel****Random Number Generator with Normal Distribution in Excel (4 Methods)**