VBA Rnd, RandBetween to Generate Random Numbers [1 to 10, 1 to 1000]

How to generate random numbers in VBA

You may use built-in functions in VBA to generate random numbers.

The first function is Rnd().
It takes an argument which is a numeric expression (optional). For example,

rand_num = Rnd()

To generate a random number between two given numbers, you may also use WorksheetFunction’s RandBetween function. For example,

Cells(x, 1).Value = WorksheetFunction.RandBetween(700, 800)

The section below shows the usage of these two functions to generate random numbers in VBA.
An Excel formula for generating random numbers between two given numeric values by Rnd() is also given below.

A simple example of generating a random number by Rnd()

We assigned the Rnd() function to a variable and displayed it in the message box, without passing a numeric value:

Sub random_ex()

Dim rand_num

rand_num = Rnd()
MsgBox rand_num

End Sub

Sample result as executed program:

If no number is supplied, the Rnd function generates the next number in the pseudo-random sequence.

Provide a number greater than zero to Rnd

Rnd() generates the next number in the pseudo-random sequence if you provide a number greater than zero:

Sub random_ex()
Dim rand_num

rand_num = Rnd(25)

MsgBox rand_num

End Sub

A result as we run that code:

You may learn more about the rules of return values here.

Generate a number between 1 and 10 by Rnd()

Because simply using Rnd() generates numbers less than 1 and greater than or equal to zero.

To generate a random greater than 1 and between two given numbers, you may use the following formula:

Int((upperbound - lowerbound + 1) * Rnd + lowerbound)

Where upperbound is the upper limit and lowerbound is the lower number that you want to generate a number.

The example below generates a random number between 1 to 10 by using this formula:

Sub random_ex()
Dim random_num

'Random num between 1 to 10
random_num = Int((10 * Rnd) + 1)

MsgBox random_num

End Sub

Result as we executed this code:

Writing ten random numbers between 1 to 1000 by Rnd() in an Excel Sheet

The program below writes ten random numbers in the Excel sheet.
For that, we executed a For..Loop ten times.
By using Rnd() function, we specified 1 to 1000 numbers as follows:

Sub random_ex()

Dim x As Integer

For x = 2 To 11 Step 1

    Cells(x, 1).Value = Int((1000 * Rnd) + 1)
Next x

End Sub

Result:

Will it work for 700 to 800 – a lower number other than 1?

Just to show generating between 700 to 800 (lower bound other than 1), see the result yourself:

Sub random_ex()
Dim x As Integer

'Random number between other than 1
For x = 2 To 11 Step 1

    Cells(x, 1).Value = Int((800 * Rnd) + 700)

Next x

End Sub

Result:

So, it does not work.

Let us use another function.

Using Worksheet.Function RandBetween function

Worksheet.Function’s RandBetween function for generating random numbers as well.

For that, let us try the same example as we failed above i.e. generating numbers between 700 to 800
That means, our lower bound is not one.
So, we will write the values of ten randomly generated numbers to Excel sheet cells (A2 to A11) and see if they remain between 700 to 800 or not.

The code:

Sub random_ex()
Dim x As Integer

'WorksheetFunction.RandBetween - other than 1
For x = 2 To 11 Step 1

    Cells(x, 1).Value = WorksheetFunction.RandBetween(700, 800)
Next x

End Sub

Result: