Excel VBA - Checking Array For Exact Match


I have an array, say Y  with elements say a1,a2,a3,a4.....a50. I would like to check whether this array is an exact match to a control array C elements a1,a2,a3,a4.....a50.

Example: Y = a1,a2,a3,a4.....a50 and C = a1,a2,a3,a4.....a50 implies exact match i.e. each element is exactly the same, as well as the sequencing.
Y = a1,b2,a3,a4.....a50  and C = a1,a2,a3,a4.....a50  implies not exact match ( e.g. a1 = apple, b2 = dog, a2 = pear).

The elements are string type e.g. apple, pear, lemon etc

Would appreciate very much help on the vba code.

Environment: Excel 2010.

Who is Participating?
drfrank319Connect With a Mentor Commented:
Option Base 1
Sub Test()
    Dim myArray1 As Variant
    Dim myArray2 As Variant
    myArray1 = Array("1", "2", "3", "4", "5", "6", "7", "8", "9", "10")
    myArray2 = Array("1", "2", "13", "4", "5", "6", "7", "8", "9", "10")
    MsgBox (CompareArrays(myArray1, myArray2))
End Sub

Function CompareArrays(myArray1 As Variant, myArray2 As Variant) As Boolean
    Dim i As Long
    Dim arraysAreIdentical As Boolean
    arraysAreIdentical = True
    If LBound(myArray1) = LBound(myArray2) And UBound(myArray1) = UBound(myArray2) Then
        For i = LBound(myArray1) To UBound(myArray1)
            If myArray1(i) <> myArray2(i) Then
                Debug.Print ("myArray1(" & i & ") and myArray2(" & i & ") are not identical.")
                arraysAreIdentical = False
                Exit For
            End If
        Debug.Print ("myArray1 has " & UBound(myArray1) & " elements and myArray2 has " & UBound(myArray2) & " elements.")
        arraysAreIdentical = False
    End If
    If arraysAreIdentical = True Then
        Debug.Print ("The arrays are identical.")
        CompareArrays = True
        Debug.Print ("The arrays are not identical.")
        CompareArrays = False
    End If
End Function
FaustulusConnect With a Mentor Commented:
This might work for you,
Private Sub StringCompare()

    Dim Y() As String
    Dim C() As String
    C = Split("a1 a2 a3 a4 a5 a6")
    Y = Split("a1 a2 a3 a4 a5 A6")
    MsgBox IIf(StrComp(Join(C), Join(Y), vbBinaryCompare), "Not same", "Same")
End Sub

Open in new window

In this example the strings aren't identical because of the capitalized A in Y(A6). You can change the compare mode to VbTextCompare to accept such differences.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.