I had a need recently to be able to take arrays of data with unpredictable orders and compare them. However, the order of the data is irrelevant and unpredictable. So, how to do it? I wrote two things: One to compare the arrays, and a bubble sort to sort the values. Elementary programming, yes, but handy nonetheless.
Private Function ArrayCompare(ByRef vArray1, ByRef vArray2, ByVal lCompareType)
Dim blnResult 'As Boolean
Dim vTemp1 'As Variant
Dim vTemp2 'As Variant
'We will assume the arrays are split already so we don't have to
'know how to split them.
'However, we will sort them here...
ArraySort vArray1, UBound(vArray1)
ArraySort vArray2, UBound(vArray2)
blnResult = True
If UBound(vArray1) = UBound(vArray2) Then
'We now know the arrays have the same number of elements
For lIndex = LBound(vArray1) to UBound(vArray1)
Select Case lCompareType
Case USESTRING
vTemp1 = CStr(vArray1(lIndex))
vTemp2 = CStr(vArray2(lIndex))
Case USELONG
vTemp1 = CLng(vArray1(lIndex))
vTemp2 = CLng(vArray2(lIndex))
End Select
If vTemp1 = vTemp2 Then
blnPass = True
Else
blnPass = False
'once we get a negative result, no need
'to continue
Exit For
End If
Next 'lIndex
Else
blnResult = False
End If
ArrayCompare = blnResult
End Function
Public Sub ArraySort(pstrArray, plngMaxItem)
Dim lIndex
Dim blnSwitched
Dim strTemp
Do
blnSwitched = False
For lIndex = 0 To plngMaxItem - 1
If pstrArray(lIndex) > pstrArray(lIndex + 1) Then
'item out of place, move it
blnSwitched = True
'Change the items out.
strTemp = pstrArray(lIndex)
pstrArray(lIndex) = pstrArray(lIndex + 1)
pstrArray(lIndex + 1) = strTemp
End If
Next
Loop While blnSwitched
End Sub