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:

**Numbers between 0-1**using RAND function:

**Numbers between 2 whole numbers**(lower and upper boundry, inclusive) using RANDBETWEEN.**Below example for numbers between 0-100.**

**Any decimal number between 2 numbers**using the RAND function. Simply follow the pattern below (replace*LOWER_BOUNDRY*and*UPPER_BOUNDRY*with your values):= LOWER_BOUNDRY + UPPER_BOUNDRY*RAND()

. Example below generating any decimal numbers between 0-100 e.g. 1.5.

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