Random Unique Number Generator Excel VBA

[Excel Fox]

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


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]

An old thread: http://www.excelfox.com/forum/f13/create-random-number-generator-vba-245/

[Rick Rothstein]

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

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]

Thanks Rick for correcting that.

Similar discussions by Rajan at Get Random List : | LinkedIn (http://www.linkedin.com/groupItem?view=&gid=3843467&type=member&item=175282658&commentID=99831727&report%2Esuccess=8ULbKyXO6NDvmoK7o030UNOYGZKrvdhBh ypZ_w8EpQrrQI-BBjkmxwkEOwBjLE28YyDIxcyEO7_TA_giuRN#commentID_998 31727)

and

Get Random List : « excelpoweruser (http://excelpoweruser.wordpress.com/2012/10/15/get-random-list/)

[Rick Rothstein]

Thanks Rick for correcting that.

Similar discussions by Rajan at Get Random List : | LinkedIn (http://www.linkedin.com/groupItem?view=&gid=3843467&type=member&item=175282658&commentID=99831727&report%2Esuccess=8ULbKyXO6NDvmoK7o030UNOYGZKrvdhBh ypZ_w8EpQrrQI-BBjkmxwkEOwBjLE28YyDIxcyEO7_TA_giuRN#commentID_998 31727)

and

Get Random List : « excelpoweruser (http://excelpoweruser.wordpress.com/2012/10/15/get-random-list/)

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 (http://vbnet.mvps.org/code/helpers/randomarray.htm)

[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 (http://vbnet.mvps.org/code/helpers/randomarray.htm)

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.