VBA: How to Use RandBetween to Generate Random Numbers

You can use the RandBetween method in VBA to generate a random integer between two specific values.

Here are two common ways to use this method in practice:

Method 1: Use RandBetween to Generate One Random Integer

Sub GenerateRandomInteger()
Range("A1") = WorksheetFunction.RandBetween(1, 100)
End Sub

This particular macro will generate one random integer between 1 and 100 in cell A1.

Method 2: Use RandBetween to Generate Several Random Integers

Sub GenerateMultipleRandomIntegers()

    Dim i As Integer
    
    For i = 1 To 10
        Range("A" & i) = WorksheetFunction.RandBetween(1, 100)
    Next i
    
End Sub

This particular macro will generate one random integer between 1 and 100 for each cell in the range A1:A10.

The following examples show how to use each of these methods in practice.

Example 1: Use RandBetween to Generate One Random Integer

Suppose we would like to generate a random number between 1 and 100 and place the result in cell A1 of our current sheet in Excel.

We can create the following macro to do so:

Sub GenerateRandomInteger()
Range("A1") = WorksheetFunction.RandBetween(1, 100)
End Sub

When we run this macro, we receive the following output:

Notice that cell A1 contains a value of 36.

Note that each time we run this macro, a different integer value between 1 and 100 could be generated.

Example 2: Use RandBetween to Generate Several Random Integers

Suppose we would instead like to generate a random integer between 1 and 100 for each cell in the range A1:A10.

We can create the following macro to do so:

Sub GenerateMultipleRandomIntegers()

    Dim i As Integer
    
    For i = 1 To 10
        Range("A" & i) = WorksheetFunction.RandBetween(1, 100)
    Next i
    
End Sub

When we run this macro, we receive the following output: