# Random Number Generator in Excel / VBA

We will explore the options to create your own random number generator in an Excel Worksheet or in VBA (Macro). You can generate randoms in 2 ways:

• Using Excel functions i.e. using the RAND or RANDBETWEEN functions
• Using VBA (Visual Basic macro) using the RANDOMIZE and RND functions

## Random Number Generator using Excel functions

To create a random number in Excel you have 3 options:

Remember that the RAND and RANDBETWEEN functions are volatile, meaning they will be recalculated on any change to the worksheet regardless if it affects the formula. This may mean you will see constant changes to these numbers. In case it affects your performance be sure to replace your random numbers with static (copy paste as values) or generate them using VBA.

## Random Numbers using VBA functions

To generate random numbers in VBA you need to use 2 functions:

• Randomize – that initializes the Rnd function with a provided seed. If you leave the argument blank it will use the actual system timer value. If you provide a certain number e.g. 10 you will always get the same sequence of random numbers. Why? Because computers use pseudo random number generators.
• Rnd – function that generates the actual random decimal numbers between 0-1.

Below a simple example:

```Dim myRandom as Double
Randomize 'Initialize the Rnd function

myRandom = Rnd 'Generate a random number between 0-1

Debug.Print myRandom 'Print the number on console e.g.  0.308616280555725 ```

## VBA Generate whole numbers

To generate whole numbers similarly like the RANDBETWEEN Excel function we need to use the VBA CInt function to convert the decimal number to an Integer:

```Dim myRandom As Double
Randomize 'Initialize the Rnd function

myRandom = CInt(Rnd * 100) 'Generate a random number between 0-100

Debug.Print myRandom 'e.g. 25
```

The above is limited to numbers starting at 0 up to the upper boundry (above 100). We can adjust the lower and upper boundries adjusting the formula above:

```Dim myRandom As Double
Randomize 'Initialize the Rnd function

myRandom = CInt(5 + Rnd * 95) 'Generate a random number between 5-100

Debug.Print myRandom 'e.g. 5
```

The above will generate numbers between 5 and 100.

## VBA Generate decimal numbers

Using similar approach as above and removing the VBA CInt function we can generate decimal numbers between any 2 given numbers:

```Dim myRandom As Double
Randomize 'Initialize the Rnd function

myRandom = 5 + Rnd * 95 'Generate a random decimal number between 5-100

Debug.Print myRandom 'e.g. 5.4242442
```