Private Function AreArraysIdentical _
(Arg1 As Variant, Arg2 As Variant) As Boolean
Dim s1, s2 As String
s1 = Join(Arg1, "")
s2 = Join(Arg2, "")
AreArraysIdentical = s1 = s2
End FunctionSub Test()
Dim Array1, Array2, Array3 As Variant
Array1 = Array(1, 2, 3, 4, 5, 6)
Array2 = Array(1, 2, 3, 4, 5, 6)
Array3 = Array(10, 2, 30, 40, 5, 65)
MsgBox AreArraysIdentical(Array1, Array2)
MsgBox AreArraysIdentical(Array1, Array3)
End SubSub Test2()
Dim Array1, Array2 As Variant
Range("a1:a50000") = "abcdefghijk"
Range("b1:b50000") = "abcdefghijk"
With Application.WorksheetFunction
Array1 = .Transpose(Range("a1:a50000"))
Array2 = .Transpose(Range("b1:b50000"))
End With
MsgBox AreArraysIdentical(Array1, Array2)
End Sub
Did you test your function on an entire column? Transposing might cause an issue?
Sub Test()
Dim blnResult As Boolean
blnResult = Sheet1.Evaluate("AND(" & _
Sheet1.Range("A1:A50000").Address(external:=True) & "=" & _
Sheet1.Range("B1:B50000").Address(external:=True) & ")")
Debug.Print blnResult
End Sub bolMisMatch = Not .Range("G1").Value = 1
.Range("E1:G" & U_LIMIT).Clear
End With
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Debug.Print "Mismatch= " & bolMisMatch & " using CORREL alone: " & Timer - Start
ReDim jaafar_1(1 To U_LIMIT)
ReDim jaafar_2(1 To U_LIMIT)
For i = 1 To UBound(ary_1)
jaafar_1(i) = ary_1(i, 1)
jaafar_2(i) = ary_2(i, 1)
Next
Dim Answer As Boolean
Start = Timer
bolMisMatch = Not AreArraysIdentical(jaafar_1, jaafar_2)
Debug.Print "Mismatch= " & bolMisMatch & " using AreArraysIdentical W/O allowing for TRanspose time: "; Timer - Start
It's effectively evaluating this CSE formula:
I think the loop approach is the best because it's not subject to the restrictions of the others. It's not necessarily faster than the evaluate method I posted though. If the function is built to simply determine whether or not the two ranges match then the loop can be optimised (like Mark did) to Exit once a mismatch is found (of course this means that it will not tell you where all of the differences are, only the first difference). This means that a worst case scenario for the loop will be when the data matches entirely because it will loop all the way through; the best case scenario would be that the 1st element in each array is different. The evaluate approach will be pretty much constant because it always checks all of the cells. This made a difference when I tested over 10 iterations - the Evaluate method came in consistently at ~ 0.87 secs whereas the looping method varied between 0.73 secs (1st element different) through to 1.35 secs (all elements the same).
