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

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:

Provide a number greater than zero to Rnd

The Rnd() function 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:

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:

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

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

• 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:

Cool, it worked!