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

Get wellsrPRO for Free

Get these macros delivered right to your spreadsheet for free

Access tutorials and import my macros without ever leaving Excel

Show me - It's free

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

If you pass the 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 Rnd function is 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.

If you have more questions, leave a comment or reach out to me through my VBA Consulting page and I’ll be happy to help.

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.

Also, please stop by my Excel Add-ins page for some great products. This is my primary source of blog revenue and the money I receive from sales helps pay to keep this website online and accessible for all to learn.

To share the skill you just learned with others, post this article on Google+, Twitter, and Facebook. Sharing on social media is how I’m able to reach and teach more people about the awesome power of VBA.

I want to thank all my readers who have already subscribed to my email list and I encourage you to go ahead and subscribe if you haven’t done so. You’ll love the freebies and the fun VBA content I send your way!


Get wellsrPRO for Free

Get these macros delivered right to your spreadsheet for free

Access tutorials and import my macros without ever leaving Excel

Show me - It's free

About Ryan Wells


Ryan Wells

Ryan Wells is a Nuclear Engineer and professional VBA Developer. He is the lead developer of several VBA applications, including PDF VBA - a leading Excel Add-in for exporting Excel Objects, like charts and tables, to PDFs. Discover more of his popular Excel Add-ins, including Mouse To Macro and CF Shapes, at his dedicated Excel Add-ins page.