Generate Random Numbers in VBA

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.

Graphical user interface, application, Word

Description automatically generated

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

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

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:

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

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.

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

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:

Table

Description automatically generated

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.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.

Post Views: 171