Non Repeating Unique Random Numbers – Fun with Excel

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.

1. Save previously generated number in an array, cell or file.
2. Reference the array every iteration
3. Check the number is already generated or not.
4. 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.