Search in arrays (linear)

 2020-07-23    Searching    0    95

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