I wrote a short VBA script in Excel to test randomized pairings of values between two arrays of equal length. In this case, the array Car() and Color() each have four values. I send each array to a Shuffle() function which returns a randomized version of the original array back to the calling program.

Sub Draw_Names()
Dim Car() As Variant, Color As Variant, i As Integer
Dim sht As Worksheet

Set sht = ActiveSheet()

Car = Array("GTO", "Charger", "MX-6", "F-150")
Color = Array("Red", "Black", "White", "Blue")

Car = Shuffle(Car)
Color = Shuffle(Color)

For i = 0 To 3
    sht.Cells(i + 1, 1) = Car(i)
    sht.Cells(i + 1, 2) = Color(i)
Next i

End Sub

Function Shuffle(arr() As Variant) As Variant
    Dim i As Integer, k As Integer, value As Integer, temp As String
    
    k = ArrayLen(arr) - 1
    randomize
    For i = k To 0 Step -1
        value = CInt(i * Rnd())
        temp = arr(value)
        arr(value) = arr(i)
        arr(i) = temp
    Next i
    Shuffle = arr
    
End Function


Public Function ArrayLen(arr As Variant) As Integer
    ArrayLen = UBound(arr) - LBound(arr) + 1
End Function

I wrote a short VBA script in Excel to test randomized pairings of values between two arrays of equal length. In this case, the array Car() and Color() each have four values. I send each array to a Shuffle() function which returns a randomized version of the original array back to the calling program.

Sub Draw_Names()
Dim Car() As Variant, Color As Variant, i As Integer
Dim sht As Worksheet

Set sht = ActiveSheet()

Car = Array("GTO", "Charger", "MX-6", "F-150")
Color = Array("Red", "Black", "White", "Blue")

Car = Shuffle(Car)
Color = Shuffle(Color)

For i = 0 To 3
    sht.Cells(i + 1, 1) = Car(i)
    sht.Cells(i + 1, 2) = Color(i)
Next i

End Sub

Function Shuffle(arr() As Variant) As Variant
    Dim i As Integer, k As Integer, value As Integer, temp As String
    
    k = ArrayLen(arr) - 1
    randomize
    For i = k To 0 Step -1
        value = CInt(i * Rnd())
        temp = arr(value)
        arr(value) = arr(i)
        arr(i) = temp
    Next i
    Shuffle = arr
    
End Function


Public Function ArrayLen(arr As Variant) As Integer
    ArrayLen = UBound(arr) - LBound(arr) + 1
End Function

I wrote a short VBA script in Excel to test randomized pairings of values between two arrays of equal length. In this case, the array Car() and Color() each have four values. I send each array to a Shuffle() function which returns a randomized version of the original array back to the calling program.

Sub Draw_Names()
Dim Car() As Variant, Color As Variant, i As Integer
Dim sht As Worksheet

Set sht = ActiveSheet()

Car = Array("GTO", "Charger", "MX-6", "F-150")
Color = Array("Red", "Black", "White", "Blue")

Car = Shuffle(Car)
Color = Shuffle(Color)

For i = 0 To 3
    sht.Cells(i + 1, 1) = Car(i)
    sht.Cells(i + 1, 2) = Color(i)
Next i

End Sub

Function Shuffle(arr() As Variant) As Variant
    Dim i As Integer, k As Integer, value As Integer, temp As String
    
    k = ArrayLen(arr) - 1
    randomize
    For i = k To 0 Step -1
        value = CInt(i * Rnd())
        temp = arr(value)
        arr(value) = arr(i)
        arr(i) = temp
    Next i
    Shuffle = arr
    
End Function


Public Function ArrayLen(arr As Variant) As Integer
    ArrayLen = UBound(arr) - LBound(arr) + 1
End Function

While Car() and Color() are declared and structured the same way, the Shuffle(Color) call generates a compilation error stating: “Type mismatch: array or user-defined type expected” If I null out the statement Color - Shuffle(Color) , the program runs and ‘ Car = Shuffle(Car) ‘ executes properly.