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.
Option Explicit 'Table Array to store generated random numbers Public Rtable(100) As Integer Public Ridx As Integer 'Clear & Reset the array storage Public Function UniqueRandomInit() Dim i As Integer For i = 0 To 100 Rtable(i) = 0 Next i Ridx = 0 End Function 'Generate non repeating unique numbers Public Function UniqueRandom(rLow As Integer, rHigh As Integer) As Integer Dim i As Integer, Rndnum As Integer, Loop_Count As Integer Loop_Count = 0 Label_Init_Random: If Ridx >= (rHigh - rLow + 1) Then MsgBox "Limit Reached: Numbers Generated(" & Ridx & ");Max Numbers Allowed(" & (rHigh - rLow + 1) & ")" UniqueRandom = 0 GoTo Label_End_Function End If Rndnum = VBA.Round(rLow + VBA.Rnd(1) * (rHigh - rLow), 0) For i = 0 To Ridx If Rndnum = Rtable(i) Then GoTo Label_Init_Random: End If Next i Ridx = Ridx + 1 Rtable(Ridx) = Rndnum UniqueRandom = Rndnum Label_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.