Check if string is in array or not including wildcards with Excel VBA

Question

I found this function to identify whether a string is in a given array, however it does not seem to be able to handle wildcards (or atleast not the way i do it).

Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
  IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)
End Function

My use of the function:

Dim BannedWildcards() As Variant

BannedWildcards = Array("", "-", "?", 0, "na", "n/a", _
"*account*", "*hse*", "*defined*", "*applicable*", "*operation*", "*action*", "*manager*")

            Select Case True
            Case IsInArray(LCase(Sht_Tracker.Cells(RowCounter_CRCT, 17)), BannedWildcards) = True
                Arr_Dashboard_Current(10, ArrC_Dashboard_Current) = "#N/A"
            Case Else: Arr_Dashboard_Current(10, ArrC_Dashboard_Current) = Sht_Tracker.Cells(RowCounter_CRCT, 17)
            End Select

This seems to be what you're looking for: http://stackoverflow.com/questions/30175061/wildcard-search-in-array — SLWS, Mar 29 '17 at 09:55
Yes and no. Some of my strings are not wildcards while others are. the one you linked to does not seem to distinguish between it. That being said, i guess the easy way would be to use two arrays one with and one without wildcards — Sebastian Koefoed, Mar 29 '17 at 10:54

score 1 · Accepted Answer · answered Mar 29 '17 at 11:03

Or something like:

Function IsInArray2(StringToBeFound As String, MyArray As Variant) As Boolean
IsInArray2 = False
wildCard = "*"
If InStr(StringToBeFound, wildCard) > 0 Then
    For i = LBound(MyArray) To UBound(MyArray)
        If "*" & MyArray(i) & "*" Like StringToBeFound Then IsInArray2 =    True 'will match MyArray to any substring of StringToBeFound
    Next
Else
    For i = LBound(MyArray) To UBound(MyArray)
            If MyArray(i) == StringToBeFound Then IsInArray2 =  True 'will exactly match MyArray to StringToBeFound
    Next
End If
End Function

score 0 · Answer 2 · answered Mar 29 '17 at 13:01

Thank you SLWS. I modified the code you referenced to a bit in order to fit my need.
This works for me:

Function IsInArray(stringToBeFound As String, MyArray As Variant) As Boolean

    Dim i As Long
    Dim WildCard As String
    WildCard = "*"

    IsInArray = False

    For i = LBound(MyArray) To UBound(MyArray)
        If InStr(MyArray(i), WildCard) > 0 Then
            If LCase(stringToBeFound) Like LCase("*" & Replace(MyArray(i), " ", "*") & "*") Then
                IsInArray = True
                Exit Function
            End If
        Else
            If LCase(stringToBeFound) = LCase(MyArray(i)) Then
                IsInArray = True
                Exit Function
            End If
        End If
    Next
End Function

Check if string is in array or not including wildcards with Excel VBA

2 Answers2