Introduction

The RND function is used to generate a random number between 0 and 1. Generating a random number has applications in domains such as gambling, statistical sampling, computer simulation, or to — generally speaking — create unpredictable results for any other purpose.

Of course, most of the time we want to be able to specify the range within which we generate the random number. In this article, we will see how we can do that, and we will also see how we can generate random numbers without repetition.

Syntax

Rnd([Number]) where

Number is optional and could have the following values:

Number	Output
Less than 0	Returns same random number on each call using [Number] as the seed.
Equal to 0	Returns the most recent random number
Greater than 0 (default)	Returns the next generated random number. This applies if blank.

Example 1: Generating a random number

To implement RND, we need to use it in combination with Int, which is a function that converts a number to an integer.

Sub Generate_Random_Number()
‘Set the maximum and minimum numbers of the range that we’re selecting the random number from
maxNumber = 100
minNumber = 1
randomNumber = Int(maxNumber * Rnd) + minNumber
MsgBox randomNumber
End Sub

If you run this code multiple times, you will notice a pattern. It seems like the series of random numbers repeat themselves every so often. If you stop generating the numbers for some time, then return later to generating them, you will notice that you’re getting the same numbers in the same order again.

Hence, We also need to use Randomize, which is a function that changes the seeding of RND every time it runs. The seed can be thought of as a number that initializes the randomizing process. Utilizing Randomize without any parameters will by default use the system current time to set the seed.

Below is the code after adding the seed. Note that if you specify a particular number, then you can reproduce the results and others can reproduce it as well, which is sometimes desirable in research.

Sub Generate_Random_Number()
‘Set the maximum and minimum numbers of the range that we’re selecting the random number from
maxLimit = 100
minLimit = 1
Randomize
randomNumber = Int(maxLimit * Rnd) + minLimit
MsgBox randomNumber
End Sub

Now every time you generate the number, it will be indeed random, you will not get any patterns. But what if you want to get unique random numbers? Follow the next example to see how you can achieve that!

Example 2: Generating a unique list of random numbers

We will utilize VBA collection in order to store the random numbers we generate. NumberCount will allow us to specify the number of unique random numbers we want to generate.

Function UniqueRandomNumbers(NumberCount As Long, LowerLimit As Long, UpperLimit As Long) As Variant
Dim RndList As Collection, i As Long, uniqueList() As Long
UniqueRandomNumbers = False
    If NumberCount> (UpperLimit - LowerLimit + 1) Then Exit Function
    Set RndList = New Collection
    Randomize

    Do
        On Error Resume Next
i = CLng(Rnd * (UpperLimit - LowerLimit) + LowerLimit)
RndList.Add i, CStr(i)
        On Error GoTo 0
    Loop Until RndList.Count = NumberCount
ReDim uniqueLIst(1 To NumberCount)
    For i = 1 To NumberCount
uniqueList(i) = RndList (i)
    Next i
    Set RndList = Nothing
UniqueRandomNumbers = uniqueList
    Erase uniqueList
End Function

The following procedure tests the function above. It places the list of unique random numbers that we get from the function into column one of the active sheet, spanning row 3 to row 202 (a total of 200 rows).

Sub Test_Procedure()
Dim rndNumberList As Variant
rndNumberList = UniqueRandomNumbers(50, 1, 100)
Range(Cells(3, 1), Cells(50 + 2, 1)).Value = _
Application.Transpose(rndNumberList)
End Sub

The output of the code is shown below.