In Excel VBA or any programming language we have built in function to generate random number. With these functions there are possibilities that the function can generate same number any number of times.
There is no limitation set on these functions. With the code in this article, we can generate a range of number, each time picking a number randomly without any repetition.
Unique Random Numbers
This is only possible by saving the previously generated number in an array, cell or file. Then reference the array for every iteration whether the number is already generated or not.
If the random number is already generated, then produce another number and repeat the process of generate + check array till the function produce a unique number.
Also Read: How to generate Random numbers in Excel?
The following Excel VBA code exactly does this process. The limitation with this code is that it generates only 100 numbers. If you need more numbers, then increase the array size.
Note: Before starting with this process or generating a new set of unique random numbers, call the UniqueRandomInit function, to reset or clear the array storage.Visual Basic123456789101112131415161718192021222324252627282930313233343536373839Option Explicit'Table Array to store generated random numbersPublic Rtable(100) As IntegerPublic Ridx As Integer'Clear & Reset the array storagePublic Function UniqueRandomInit()Dim i As IntegerFor i = 0 To 100Rtable(i) = 0Next iRidx = 0End Function'Generate non repeating unique numbersPublic Function UniqueRandom(rLow As Integer, rHigh As Integer) As IntegerDim i As Integer, Rndnum As Integer, Loop_Count As IntegerLoop_Count = 0Label_Init_Random:If Ridx >= (rHigh - rLow + 1) ThenMsgBox "Limit Reached: Numbers Generated(" & Ridx & ");Max Numbers Allowed(" & (rHigh - rLow + 1) & ")"UniqueRandom = 0GoTo Label_End_FunctionEnd IfRndnum = VBA.Round(rLow + VBA.Rnd(1) * (rHigh - rLow), 0)For i = 0 To RidxIf Rndnum = Rtable(i) ThenGoTo Label_Init_Random:End IfNext iRidx = Ridx + 1Rtable(Ridx) = RndnumUniqueRandom = RndnumLabel_End_Function:End Function
This kind of Unique Random number is used in game sequence generation, where the code while in execution has to pick set of predefined game rules in random sequence but without repeating the same rule again.
It is also used in quiz or online tests where the participants will be asked a question from predefined question list, but choosing question randomly. It should be taken care that same question should not be repeated. Like this, there are many uses of Unique random number generation.
Previous Post: WhatsApp Crash – How to Fix?
Next Post: Facebook Basic Free Internet Efforts Failed – Net Neutrality Prevails
Non Repeating Unique Random Numbers – Fun with Excel