Excel VBA - Checking Array For Exact Match

Hi,

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.

Thanks
Who is Participating?

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
Next
Else
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
Else
Debug.Print ("The arrays are not identical.")
CompareArrays = False
End If
End Function
0

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