Search in arrays (linear)
2020-07-23 Searching 0 372
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