Random Unique Number Generator Excel VBA

[Rick Rothstein]

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]

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).

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.