Random Unique Number Generator Excel VBA

**Excel Fox** · 10-17-2012, 01:22 AM

Here's a way to generate unique random numbers from a minimum value to a maximum value

Code:

Sub GenerateRandomUnique()

    Dim lng As Long
    Const lngMax As Long = 100
    Const lngMin As Long = 1
    With CreateObject("Scripting.Dictionary")
        Do While .Count <> lngMax
            lng = Rnd * (lngMax - lngMin + 1) + 1
            .Item(lng) = Empty
        Loop
        MsgBox Join(.Keys, " ")
    End With
    
End Sub

**Admin** · 10-17-2012, 06:52 AM

An old thread: http://www.excelfox.com/forum/f13/cr...rator-vba-245/

**Rick Rothstein** · 10-17-2012, 09:13 PM

Originally Posted by Excel Fox

Here's a way to generate unique random numbers from a minimum value to a maximum value

Assuming you meant the "from a minimum to a maximum value" to mean the user can set those values to their own values, I think your code is not correct. I believe this modification to it will perform correctly...

Code:

Sub GenerateRandomUnique()    Dim lng As Long
    Const lngMax As Long = 100
    Const lngMin As Long = 1
    With CreateObject("Scripting.Dictionary")
        Do While .Count <= lngMax - lngMin          'removed the greater than symbol
            lng = Rnd * (lngMax - lngMin) + lngMin  'removed the +1
            .Item(lng) = Empty
        Loop
        MsgBox Join(.Keys, " ")
    End With
End Sub

**Excel Fox** · 10-18-2012, 12:30 PM

**Rick Rothstein** · 10-18-2012, 12:50 PM

Originally Posted by Excel Fox

Thanks Rick for correcting that.

Similar discussions by Rajan at Get Random List : | LinkedIn

and

Get Random List : « excelpoweruser

Here is something I originally posted to the old compiled VB newsgroups back in 1999 (according to the acticle header) which was subsequently picked up by this compiled VB website. My code is the subroutine named RandomizeArray embedded within the "housing" the website owner put it in...

[VBnet Helpers] Pure VB: Generating a Random Array of Unique Numbers

**Rick Rothstein** · 10-18-2012, 01:00 PM

Originally Posted by Rick Rothstein

Here is something I originally posted to the old compiled VB newsgroups back in 1999 (according to the acticle header) which was subsequently picked up by this compiled VB website. My code is the subroutine named RandomizeArray embedded within the "housing" the website owner put it in...

[VBnet Helpers]* Pure VB: Generating a Random Array of Unique Numbers

Actually, instead of making you wade through the demo code at the above site, here is one of the few ways I have presented my randomizing routine in the past...

The following is a generalized shuffling routine that I have posted in the past over in the compiled VB newsgroups, but it works fine in the VBA world of Excel as well. Assign your values to an array and then pass that into the RandomizeArray subroutine and it will put elements of the array into a random order and return the randomized elements back in the original array that was passed to it. So, to read out the randomize list, just read the array from its lower bound to whatever number of random elements you need (up to the maximum of the array's upper bound). It only visits *each* array element *once* so it is quick. The code takes care of running the Randomize statement one time only (which is all that is necessary).

Code:

Sub RandomizeArray(ArrayIn As Variant)
  Dim X As Long, RandomIndex As Long, TempElement As Variant
  Static RanBefore As Boolean
  If Not RanBefore Then
    RanBefore = True
    Randomize
  End If
  If VarType(ArrayIn) >= vbArray Then
    For X = UBound(ArrayIn) To LBound(ArrayIn) Step -1
      RandomIndex = Int((X - LBound(ArrayIn) + 1) * Rnd + LBound(ArrayIn))
      TempElement = ArrayIn(RandomIndex)
      ArrayIn(RandomIndex) = ArrayIn(X)
      ArrayIn(X) = TempElement
    Next
  Else
    'The passed argument was not an array, so put error handler here, such as . . .
    Beep
  End If
End Sub

After passing your array into the RandomizeArray subroutine, its elements will be randomly reordered. The passed array may be of any normal type -- integer, string, single, etc. The neat thing is, if you pass an already randomized array to this routine, those randomly ordered elements will be randomize -- sort of like shuffling an already shuffled deck of cards.