How to Generate Random Numbers in VBA

In VBA, there are different ways that you can use to generate a random number in Excel, and in this post, we will look at all of them one by one.

RND Function

To generate a random number, in VBA, there is a function called RND. This function stands for random and when you use this function in code it returns a random number between 0 to 1. In RND, you don’t need to specify any argument.

Range("A1") = Rnd()

The above code uses it to get a random number in the A1 and it has returned 0.705547521.

But when you use it for an entire range it returns the same random number in the entire range and that makes it void for use to generate random numbers more than once.

Now here it doesn’t make sense to use it, right? But there’s a solution that you can use, FOR NEXT LOOP.

In the above code, you have a loop that loops through 10 cells (one by one) from the selected cell and adds a random number.

Here’s the full code:

Sub vba_random_number()
Dim i As Long
i = 10
For i = 1 To i
    ActiveCell.Value = Rnd()
    ActiveCell.Offset(1, 0).Select
Next i
End Sub

Random Number Between Two Numbers

If you want to generate a random number between two defined numbers, in that case, you need to write code by using two VBA Functions (RND + INT). The code for this formula would be like below.

Sub vba_random_number()
Dim myRnd As Integer
myRnd = Int(2 + Rnd * (45 - 10 + 1))
Range("A1") = myRnd
End Sub

When you run this macro, RND uses the max number and the min number that you have defined to generate the random number and INT removes the decimal from that.

Using Worksheet Functions in a VBA Code to Generate a Random Number

In VBA, you can access worksheet functions and use them to get a result by specifying the arguments. There are two functions from the worksheet that can help you to get a random number in the result.

• RandBetween
• RandArray

Before you use these functions make sure to understand the arguments that you need to define. Now in the below code, you have RANDBETWEEN which generates a random number between two numbers.

Range("A1") = WorksheetFunction.RandBetween(1000, 2000)

Here the max number is 2000 and the min number is 1000. In the same way, you can use the RANDARRAY which is dynamic arrays function.

Here’s the code.

Range("A1:B10") = WorksheetFunction.RandArray(10, 2, 1000, 2000, True)

If you look at the syntax of the RANDARRAY, you will find that you can enter random numbers in an entire range which is not possible with all the methods that we have discussed above.