Generating random numbers in Excel VBA can be helpful in different scenarios. For example, to create a random list of numbers for statistical data analysis or select lucky winners of prizes from a list.

This tutorial shows how to generate random numbers in Excel VBA using the built-in **RND** function or the **RANDBETWEEN **and **RANDARRAY** worksheet functions.

## Generate Random Numbers in Excel VBA Using the Built-in RND Function

We can use the VBA built-in **RND** function to generate random numbers between 0 and 1. Additionally, we can use the function to generate random integers within a particular range.

### Example #1: Use the RND Function to Generate Random Numbers Between 0 and 1

We can use the RND function to generate random numbers between 0 and 1.

In this example, we want to generate fifteen random numbers between 0 and 1 using the following steps:

- On the active Excel worksheet, press
**Alt + F11**to open the**Visual Basic Editor**. - Open the
**Insert**menu and choose**Module**to insert a module.

- Copy the below sub-procedure and paste it into the module:

1 2 3 4 5 6 7 8 |
Sub VBARandomNumbers() Dim i As Long i = 15 For i = 1 To i ActiveCell.Value = Rnd() ActiveCell.Offset(1, 0).Select Next i End Sub |

- Save the workbook as an
**Excel****Macro-Enabled Workbook (*.xlsm)**. - Place the cursor anywhere in the procedure and press
**F5**to run the code. - Press
**Alt + F11**to switch to the active worksheet to see the following fifteen random numbers produced by the code.

### Example #2: Use the RND Function to Generate Random Integers Within a Specific Range

You can use the **RND** function with some arithmetic operations to generate random integer numbers within a specific range. For example, to generate a random number between 10 and 45, you can use the following steps:

- Repeat steps 1-2 of Example #1 to insert a new module.
- Copy the following sub-procedure and paste it into the module:

1 2 3 4 5 6 7 8 |
Sub VBARandomNumbersInRange() Dim i As Long i = 15 For i = 1 To i ActiveCell.Value = Int((45 - 10 + 1) * Rnd + 10) ActiveCell.Offset(1, 0).Select Next i End Sub |

- Save the workbook as an
**Excel Macro-Enabled Workbook (*.xlsm)**. - Place the cursor anywhere in the sub-procedure and press
**F5**to run the code. - Press Alt +F11 to switch to the active worksheet to see the following random integers generated by the code:

## Use Worksheet Functions to Generate Random Numbers in Excel VBA

We can use the **RANDBETWEEN **and **RANDARRAY** worksheet functions to generate random numbers in Excel VBA.

### Example #3: Use the RANDBETWEEN Worksheet Function to Generate random Numbers in Excel VBA

The RANDBETWEEN function generates a random number between specific numbers. For example, we can generate fifteen random numbers between 2000 and 3000 using the following steps:

- Repeat steps 1-2 of Example #1 to insert a module.
- Copy the following sub-procedure into the new module.

1 2 3 4 5 6 7 8 |
Sub VBARandomNumbersBetween() Dim i As Long i = 15 For i = 1 To i ActiveCell.Value = WorksheetFunction.RandBetween(2000, 3000) ActiveCell.Offset(1, 0).Select Next i End Sub |

- Save the workbook as an
**Excel Macro-Enabled Workbook (*.xlsm)**. - Place the cursor in the sub-procedure and press
**F5**to run the code. - Press
**Alt + F11**to switch to the current worksheet to see the following fifteen random numbers generated by the code:

### Example #4: Use the RANDARRAY Worksheet Function to Generate random Numbers in Excel VBA

The **RANDARRAY **function generates an array of random numbers. For example, we can generate thirty random numbers between 2000 and 3000 using the following steps:

- Repeat steps 1-2 of Example #1 to insert a module.
- Copy the following sub-procedure into the new module.

Sub VBARandomArray()

Range(“A1:B15”) = WorksheetFunction.RandArray(15, 2, 2000, 3000, True)

End Sub

- Save the workbook as an
**Excel Macro-Enabled Workbook (*.xlsm)**. - Place the cursor in the sub-procedure and press
**F5**to run the code. - Press
**Alt + F11**to switch to the current worksheet to see the following thirty random numbers in two columns generated by the code:

**Note:** When we look at the following syntax of the RANDARRAY function …

We notice that, unlike the other methods we have discussed, we can simultaneously use the RANDARRAY function to enter random numbers in many cell ranges.

## Conclusion

Generating random numbers in Excel VBA can be helpful in various applications, from statistical analysis to selecting lucky winners from a list. This tutorial looked at four examples of generating random numbers in Excel VBA. We hope you found the tutorial helpful.