### Search in arrays (linear)

2020-07-23    Searching    0

With the functions below you can search for an item in a one- or two-dimensional array.
A linear search like this will probably be very slow if the array you are searching is large or you perform many searches.
If your array is sorted in ascending order you can use a binary search to find items in an array significantly faster.

```Function Array_Find(varArray As Variant, varFind As Variant, _
Optional lngCompareMethod As VbCompareMethod = vbBinaryCompare, _
Optional blnReturnFirst As Boolean = True) As Long
' updated 2008-04-30 by OPE
' performs a linear search in a one-dimensional array and returns the index for the first or last item that matches varFind
' returns -1 if varArray is not an array or if varFind was not found in varArray, optionally LBound(varArray) - 1 if LBound(varArray) <= -1
' example: i = Array_Find(varArray, 1234)
' example: i = Array_Find(varArray, "somethingtofind", vbTextCompare)
Dim i As Long
Array_Find = -1
If Not IsArray(varArray) Then Exit Function

If LBound(varArray) <= -1 Then Array_Find = LBound(varArray) - 1
i = UBound(varArray) - LBound(varArray) + 1 ' array items count
If i < 1 Or Len(varFind) = 0 Then Exit Function ' no array items or nothing to find

If blnReturnFirst Then
For i = LBound(varArray, 1) To UBound(varArray, 1)
If lngCompareMethod = vbBinaryCompare Then
If varArray(i) = varFind Then
Array_Find = i
Exit Function
End If
Else ' compare text
If StrComp(varArray(i), varFind, lngCompareMethod) = 0 Then
Array_Find = i
Exit Function
End If
End If
Next i
Else
For i = UBound(varArray, 1) To LBound(varArray, 1) Step -1
If lngCompareMethod = vbBinaryCompare Then
If varArray(i) = varFind Then
Array_Find = i
Exit Function
End If
Else ' compare text
If StrComp(varArray(i), varFind, lngCompareMethod) = 0 Then
Array_Find = i
Exit Function
End If
End If
Next i
End If
End Function

Function Array_Find2(varArray As Variant, varFind As Variant, Optional lngCompareColumn As Long = -1, _
Optional lngCompareMethod As VbCompareMethod = vbBinaryCompare, Optional blnReturnFirst As Boolean = True) As Long
' updated 2008-04-30 by OPE
' performs a linear search in a two-dimensional array and returns the index for the first or last item in column lngCompareColumn that matches varFind
' lngCompareColumn: must be a value >= LBound(varArray, 2) and <= UBound(varArray, 2)
' if lngCompareColumn < 0 then LBound(varArray, 2) (the first column) will be used
' returns -1 if varArray is not an array or if varFind was not found in varArray, optionally LBound(varArray, 1) - 1 if LBound(varArray, 1) <= -1
' example: i = Array_Find2(varArray, 1234, 1)
' example: i = Array_Find2(varArray, "somethingtofind", 2, vbTextCompare)
Dim i As Long
Array_Find2 = -1
If Not IsArray(varArray) Then Exit Function

If LBound(varArray, 1) <= -1 Then Array_Find2 = LBound(varArray, 1) - 1
i = UBound(varArray, 1) - LBound(varArray, 1) + 1 ' array items count
If i < 1 Or Len(varFind) = 0 Then Exit Function ' no array items or nothing to find

If lngCompareColumn < 0 Then lngCompareColumn = LBound(varArray, 2)
If lngCompareColumn < LBound(varArray, 2) Or lngCompareColumn > UBound(varArray, 2) Then Exit Function

If blnReturnFirst Then
For i = LBound(varArray, 1) To UBound(varArray, 1)
If lngCompareMethod = vbBinaryCompare Then
If varArray(i, lngCompareColumn) = varFind Then
Array_Find2 = i
Exit Function
End If
Else ' compare text
If StrComp(varArray(i, lngCompareColumn), varFind, lngCompareMethod) = 0 Then
Array_Find2 = i
Exit Function
End If
End If
Next i
Else
For i = UBound(varArray, 1) To LBound(varArray, 1) Step -1
If lngCompareMethod = vbBinaryCompare Then
If varArray(i, lngCompareColumn) = varFind Then
Array_Find2 = i
Exit Function
End If
Else ' compare text
If StrComp(varArray(i, lngCompareColumn), varFind, lngCompareMethod) = 0 Then
Array_Find2 = i
Exit Function
End If
End If
Next i
End If
End Function
```