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
```

Write better macros in half the time

I see people struggling with Excel every day and I want to help. **That's why I'm giving away my personal macro library for free.** This powerful gift lets you automatically import all *my macros* directly into *your spreadsheet* with just one click.

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

`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 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!