Analyst Cave

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:

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:

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
Exit mobile version