Try this. Check the function 'testArray' for an example on how to use

the code.

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

' ShuffleArrayInPlace

' This shuffles InArray to random order, randomized in place.

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim N As Long

Dim L As Long

Dim Temp As Variant

Dim J As Long

Randomize

L = UBound(InArray) - LBound(InArray) + 1

For N = LBound(InArray) To UBound(InArray)

J = Int((UBound(InArray) - LBound(InArray) + 1) * Rnd +

LBound(InArray))

If N <> J Then

Temp = InArray(N)

InArray(N) = InArray(J)

InArray(J) = Temp

End If

Next N

End Function

Private Function testArray()

Dim x() ' Define x as an array

x = Array(1, 2, 3, 4, 5) ' Populate array with numbers from 1 to 5

ShuffleArrayInPlace x() ' Shuffle array

Debug.Print "First number is " & x(0)

Debug.Print "Second number is " & x(1)

Debug.Print "Third number is " & x(2)

Debug.Print "Fourth number is " & x(3)

Debug.Print "Fifth number is " & x(4)

End Function- Hide quoted text -

- Show quoted text -

Sorry, the function definition for ShuffleArrayInPlace got cut off

when I copied in my previous post.

Private Function ShuffleArrayInPlace(InArray() As Variant)

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

' ShuffleArrayInPlace

' This shuffles InArray to random order, randomized in place.

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim N As Long

Dim L As Long

Dim Temp As Variant

Dim J As Long

Randomize

L = UBound(InArray) - LBound(InArray) + 1

For N = LBound(InArray) To UBound(InArray)

J = Int((UBound(InArray) - LBound(InArray) + 1) * Rnd +

LBound(InArray))

If N <> J Then

Temp = InArray(N)

InArray(N) = InArray(J)

InArray(J) = Temp

End If

Next N

End Function

Private Function testArray1()

Dim x() ' Define x as an array

x = Array(1, 2, 3, 4, 5) ' Populate array with numbers from 1 to 5

ShuffleArrayInPlace x() ' Shuffle array

Debug.Print "First number is " & x(0)

Debug.Print "Second number is " & x(1)

Debug.Print "Third number is " & x(2)

Debug.Print "Fourth number is " & x(3)

Debug.Print "Fifth number is " & x(4)

End Function