How to Generate Unique Random No. using Excel VBA (2 Example)

Generate Unique Random No. using Excel VBA

DOWNLOAD USED EXCEL FILE FROM HERE>>

In today’s post, we will explain to you that Generate Unique Random No. using Excel VBA, as we told in the previous post that you How to Generate Random No. Between 2 No. in Excel VBA. In this article, we will create a custom function to generate a list of unique random no. As we mentioned in the previous post, it takes only number argument to Generate unique random No. This is also an optional parameter. This will generate random No. greater than 0 and less than 1.

It works similar to the Excel “RAND” function. As we said in the worksheet function “RAND” in VBA, we can Generate Unique Random No. that are greater than 0 but less than 1.

If you want to generate a random number between two defined numbers, in that case, you have to write code using two VBA functions (RND + INT).

Let us now understand How to Generate Unique Random No. in Excel VBA.

How to Generate Unique Random No. using Excel VBA

Well, you all must know how to generate random no. in excel sheet by using RAND function, now we will learn in this article. Let us understand how to Generate unique random No. in Excel VBA. So, we have to go like last time, first go to the Developer Tab then click on the option of Visual Basic as shown in the image below.

On opening in VBE, you have to go to Insert and then Module has to be inserted, as can be seen in the image.

We are using dictionary here so we should enable Microsoft Runtime Library. To activate go to Tools option and then References and on that we select Microsoft Script Runtime Library and click on OK.

Once the module is inserted, and the library is activated, we’ll write a subroutine to Generate Unique Random No.

Sub RandNoUnique()

End Sub

Here we are Declaring variables for minimum Number & Maximum Number.

Sub GenerateNumber()
Dim MaxNum As Double
Dim MinNum As Double

End Sub

Declaring more variables which are using in the entire project for Random Number and long.

Sub RandNoUnique()
Dim MinNo As Double
Dim MaxNo As Double
Dim rndNo As Double
Dim lim As Long

End Sub

Here we are Declaring Instant variables for Dictionary.

Sub UseDictionary()
Dim MinNo As Double
Dim MaxNo As Double
Dim Dic As New Dictionary
Dim rndNo As Double
Dim lim As Long
Dim Dic As New Dictionary
End Sub

Then here we will give the minimum and maximum numbers between which we want to generate unique random no.

Sub RandNoUnique()
Dim MinNo As Double
Dim MaxNo As Double
Dim Dic As New Dictionary
Dim rndNo As Double
Dim lim As Long

MinNo = 10
MaxNo = 80

End Sub

We’ll put a limit here on how many random no. we want.

Sub RandNoUnique()
Dim MinNo As Double
Dim MaxNo As Double
Dim Dic As New Dictionary
Dim rndNo As Double
Dim lim As Long

MinNo = 10
MaxNo = 80
lim = 10 'How Many random no. needs 

End Sub

Now assign the value to the variable ‘RndNum’ through the RND Function. Will use round function with parameter, with this we will print only 2 digit after decimal.

Sub RandNoUnique()
Dim MinNo As Double
Dim MaxNo As Double
Dim Dic As New Dictionary
Dim rndNo As Double
Dim lim As Long

MinNo = 10
MaxNo = 80

lim = 10 'How Many random numbers need

rndNo = Round (2 + Rnd * (MaxNo - MinNo + 1),2)
End Sub

Then we will use do while loop, when we want to repeat certain set of statement as long as the condition is true. this will run as long as generated No. is greater than minimum number or generated No. is less than maximum number.

Sub RandNoUnique()
Dim MinNo As Double
Dim MaxNo As Double
Dim Dic As New Dictionary
Dim rndNo As Double
Dim lim As Long

MinNo = 10
MaxNo = 80

lim = 10 'How Many random numbers need 

rndNo = Round (2 + Rnd * (MaxNo - MinNo + 1),2)
Do While rndNo < MinNo Or rndNo > MaxNo
   Round (2 + Rnd * (MaxNo - MinNo + 1),2)
Loop
End Sub

Then we will use If Statement with dictionary Exist function, if value does not exist in the dictionary, then it will be added.

Sub RandNoUnique()
Dim MinNo As Double
Dim MaxNo As Double
Dim Dic As New Dictionary
Dim rndNo As Double
Dim lim As Long

MinNo = 10
MaxNo = 80

lim = 10

RndNo = Round(2 + Rnd * (MaxNo - MinNo + 1), 2)

Do While rndNo < MinNo Or rndNo > MaxNo
   RndNo = Round(2 + Rnd * (MaxNo - MinNo + 1), 2)
Loop
If Not Dic.Exists(rndNo) Then
Dic.Add rndNo, ""

End If
End Sub

Then we will create a section here which will run it like a loop, If the value of dictionary count is more than the limit then it will go to GenNo: Section

Sub RandNoUnique()
Dim MinNo As Double
Dim MaxNo As Double
Dim Dic As New Dictionary
Dim RndNo As Double
Dim lim As Long

MinNo = 10
MaxNo = 80
lim = 10

GenNo:
RndNo = Round(2 + Rnd * (MaxNo - MinNo + 1), 2)

Do While RndNo < MinNo Or RndNo > MaxNo
   RndNo = Round(2 + Rnd * (MaxNo - MinNo + 1), 2)
Loop

If Not Dic.Exists(RndNo) Then
   Dic.Add RndNo, ""
Debug.Print RndNo
End If

If Dic.Count < lim Then
GoTo GenNo
End If
End Sub

Click on run button, and the value will be printed in the immediate window.

Here you can see that some values are printed in which all values are numbers between 10 to 80. All numbers are in decimal.

Now we will use the count function to see how many numbers are being printed, along with this we will increase the limit to 20.

Sub RandNoUnique()
Dim MinNo As Double
Dim MaxNo As Double
Dim Dic As New Dictionary
Dim RndNo As Double
Dim lim As Long

MinNo = 10
MaxNo = 80

lim = 20

GenNo:

RndNo = Round(2 + Rnd * (MaxNo - MinNo + 1), 2)

Do While RndNo < MinNo Or RndNo > MaxNo
RndNo = Round(2 + Rnd * (MaxNo - MinNo + 1), 2)
Loop

If Not Dic.Exists(RndNo) Then
Dic.Add RndNo, ""
Debug.Print Dic.Count & "-"; RndNo
End If

If Dic.Count < lim Then
GoTo GenNo
End If

Click on run button, and the value will be printed in the immediate window.

As soon as the code runs you can see that 20 random values have been printed because we have increased the limit. All values are numbers between 10 and 80. All numbers are in decimal.

So, I hope you have understood How to Generate Unique Random No. in Excel VBA and for more information, you can follow us on Twitter, Instagram, LinkedIn, and YouTube as well.

DOWNLOAD USED EXCEL FILE FROM HERE>>

LEARN MORE TOPIC IN VBA HERE

You can also see well-explained video here about How to Generate Unique Random No. in Excel VBA