Non-Repeating Numbers – With Excel RND function
In Excel VBA or any programming language we have built in function to generate random number.
But it will generate same number any number of times.
With the code in this article, You can generate a unique number from a range of predefined number. Each time picking a number randomly without any repetition.
Unique Random Numbers
This is the step by step process that is used in the Unique Random generation VBA code.
- Save previously generated number in an array, cell or file.
- Reference the array every iteration
- Check the number is already generated or not.
- If the random number is already generated, then produce another number
Repeat the above steps till the function produces a unique number.
Also Read: How to generate Random numbers in Excel?
Excel VBA code – Create Random Number – Non-Repetitive
The following Excel VBA code exactly does this process.
Limitation: It generates only 100 numbers. If you need more numbers, 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.Create Unique Random Numbers in Excel RNd functionVisual 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
Use UniqueRandomInit function only during start of the execution. Call UniqueRandom whenever you need a special number.
Use of Random Numbers in Excel
Game sequence generation logic uses this kind of Unique Random numbers.
- The code has to pick set of predefined game rules in random sequence but without repeating the same rule again.
- Quiz or online test automation uses these logic as well.
- Pick random question from predefined question list, but choosing question randomly.
- This way – same question will not be repeated & also randomized.
Like this, there are many uses of Unique random number generation.
Previous Post: WhatsApp Crash – How to Fix? – Failure Recover
Next Post: Universal VCF to Excel and Excel to Vcard Converter
Non Repeating Unique Random Numbers – Fun with Excel