Public Function MultiSearch(ByRef SearchedList As String, ByRef SoughtList As String, _
Optional ByRef ListDelimiter As Variant) As Boolean
' Returns TRUE if any element in SoughtList matches an element in SearchedList.
' The comparison is case-insensitive (arguments are converted to lower case for the check).
' Items in Soughtlist are encapsulated with the delimiter to ensure full matches only.
' Returns FALSE if there is no match or if an argument is in error.
' This function is commutative, but will work faster if both lists are sorted alphabetically
' and the largest set passed as SearchedList.

Dim SoughtArray() As String, strSearched As String, Delimiter As String
Dim i As Long

MultiSearch = False

'Identify delimiter. Use "," by default.
If IsMissing(ListDelimiter) Then
Delimiter = ","
Else
Delimiter = CStr(ListDelimiter)
End If

'Encapsulate list to get exact matches only
If IsError(SearchedList) Then
Exit Function
Else
strSearched = Delimiter & LCase(SearchedList) & Delimiter
End If
'Break down items that will be sought
If IsError(SoughtList) Then
Exit Function
Else
SoughtArray = Split(LCase(SoughtList), Delimiter)
End If

For i = 0 To UBound(SoughtArray)
'Search items individually, encapsulated to ensure we get exact matches only
'Since the exact position is irrelevant, a binary search is done for better performance.
MultiSearch = (InStrB(strSearched, Delimiter & SoughtArray(i) & Delimiter) <> 0)
If MultiSearch Then Exit For
Next i

End Function

 

Реклама