VBA Random Number with Rnd and Randomize

Introduction to VBA Random Numbers
VBA Rnd Function
VBA Randomize Statement
Custom Random Number Generator Functions
Closing Thoughts

Introduction to VBA Random Numbers

You can generate random numbers in VBA by using the Rnd function. All random number generators rely on an underlying algorithm, usually fed by what’s called a seed number. You can use the Randomize statement to create a new seed number to ensure your random numbers don’t follow a predictable pattern.

I’ll show you what I mean in this tutorial.

VBA Rnd Function

The VBA Rnd function produces a random number between 0 and 1, excluding exactly 1. Here’s a basic example:

Sub RandomNumberGenerator()
r = Rnd
End Sub

Make powerful macros with our free VBA Developer Kit

It’s easy to copy and paste a macro like this, but it’s harder make one on your own. To help you make macros like this, we built a free VBA Developer Kit and wrote the Big Book of Excel VBA Macros full of hundreds of pre-built macros to help you master file I/O, arrays, strings and more - grab your free copy below.

The VBA Rnd function accepts an optional argument, like this: Rnd[Number].

These are the things you need to keep in mind when passing numbers to the VBA Rnd function:

• If you pass the VBA Rnd function a number less than 0, it will generate the same random number, using whatever negative value you pass it as the seed number.
• If you pass it a 0, it will repeat the most recent random number the function gave you. In other words, each time you run it with a 0, it will give you the same number corresponding to the last answer it gave you.
• If you omit the argument or pass the function a number greater than 0, it will simply give you a new random number - the next random number in the sequence.

I know it sounds odd to say the next random number in the sequence. It implies the the random numbers aren’t really random. Well, they aren’t.

Remember in my introduction when I said random number generators rely on an underlying algorithm fed by seed numbers? The VBA random numbers using the Rnd function are no exception. If you don’t pass the Rnd function an argument, it will produce the exact same random numbers each time you restart Excel and execute it. Don’t believe me? I’ll prove it.

Exit Excel, open it again, and run the following macro 3 times:

Sub RandomNumberGenerator2()
Debug.Print Rnd
End Sub

When I do it, it produces these results:

0.7055475 
0.533424 
0.5795186

Now, exit Excel again, open it again and run the exact same macro 3 more times. You’ll get the same results again:

0.7055475 
0.533424 
0.5795186

That doesn’t seem very random, does it? It’s not! Fortunately, there’s a way to make it more random by using the Randomize statement.

VBA Randomize Statement

The Randomize statement is a simple one liner that gives the Rnd function a new seed number based on your computer’s system timer. It’s really simple to use! All you have to do is type Randomize on a line in your macro, like this:

Sub RandomNumberGenerator3()
Randomize
r = Rnd
End Sub

You can pass Randomize an argument, but let’s not worry about that.

Because Randomize creates a new seed number based on your computer’s system time and the system time is always changing, you have eliminated the likelihood of producing random numbers in a predictable pattern.

To prove it, run the same experiment we ran earlier, but this time include the Randomize statement in your macro.

Exit Excel, open it again, and run the following macro 3 times:

Sub RandomNumberGenerator4()
Randomize
Debug.Print Rnd
End Sub

I got this result:

0.9445764 
0.5217053 
0.320287

Now, exit Excel again, open it again and run the exact same macro 3 more times. This time, I got completely different answers:

0.789654 
0.8799664 
0.4714864

The Randomize statement works!

Custom Random Number Generator Functions

Rnd with Randomize

If you don’t want to type Randomize each time you want a random number, you can create your own function, like this:

Function Random() As Single
Randomize
Random = Rnd
End Function

Copy and paste this into a module and now when you want a random number, you can just call the Random function, like this:

r = Random

Random Number between 2 Numbers

Sometimes you don’t want a random number between 0 and 1. Actually, a lot of times you don’t want a random number between 0 and 1! That’s why Excel comes with a worksheet function called RANDBETWEEN. You have two choices for finding a random number between two numbers.

If available, you can either call the RANDBETWEEN function by using Application.WorksheetFunction, or you can create your own user-defined function using VBA. I’ll show you how you can do both.

Worksheet Function RANDBETWEEN

The following example creates a random integer between 0 and 10.

r = Application.WorksheetFunction.RandBetween(0, 10)

VBA User-Defined Function

I prefer to use my own function so I can fully take advantage of the Randomize statement. Here’s a custom function you can copy and paste into your own module to generate a random integer between two numbers

Function RndBetween(Low, High) As Integer
   Randomize
   RndBetween = Int((High - Low + 1) * Rnd + Low)
End Function

To call this custom VBA random number generator function, you would type something like:

r = RndBetween(0, 10)

Like the example above, this sample also produces a random integer between 0 and 10.

Closing Thoughts

That’s all I have for this VBA tutorial. Today I showed you how to generate random numbers in VBA, how to eliminate falling into the repeat random numbers trap, and I showed you how to produce random numbers between two integers using Excel VBA.

Now, go off and use these random numbers in whatever program you’re working on! I find myself using them most often in my Monte Carlo calculations.

That’s all for this tutorial. When you’re ready to take your VBA to the next level, subscribe using the form below.