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