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:

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

Related posts: