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
Dim RandomIndex As Long
Dim 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
'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.
