Excel random number generator

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:
    Excel RAND function
  • Numbers between 2 whole numbers (lower and upper boundry, inclusive) using RANDBETWEEN. Below example for numbers between 0-100.
    Excel RANDBETWEEN function
  • Any decimal number between 2 numbers using the RAND function. Simply follow the pattern below (replace LOWER_BOUNDRY and UPPER_BOUNDRY with your values):

    . Example below generating any decimal numbers between 0-100 e.g. 1.5.
    Excel RANDBETWEEN function for any numbers

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

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.