Excel VBA like operator [closed]

I am using excel VBA to search for a substring in another string as below.

Dim pos As Integer
pos = InStr("I am studying at University of Texas at Arlington", "University of Texas")

If pos returns a non-negative value, it means I have the substring in the string. However, I need a more sophisticated search, where the substring can be "Univ of Tex":

InStr("I am studying at University of Texas at Arlington", "Univ of Tex")

Doesn't work for that.

Based on the maximum search terms, I need to say the substring is present. Is it possible using excel VBA?

Solution 1:

The Like operator is already available in VBA:

If "I am studying at University of Texas at Arlington" Like "*Univ*of*Texas*" Then
    MsgBox "Yes, it is!"
End If

Solution 2:

Try this:

Public Function StringContainsAny(string_source As String, _
                                  ByVal caseSensitive As Boolean, _
                                  ParamArray find_values()) As Boolean

    Dim i As Integer, found As Boolean

    If caseSensitive Then

        For i = LBound(find_values) To UBound(find_values)
            found = (InStr(1, string_source, _
                find_values(i), vbBinaryCompare) <> 0)
            If found Then Exit For
        Next

    Else

        For i = LBound(find_values) To UBound(find_values)
            found = (InStr(1, LCase$(string_source), _ 
                LCase$(find_values(i)), vbBinaryCompare) <> 0)
            If found Then Exit For
        Next

    End If

    StringContainsAny = found

End Function

Usage:

Dim SomeString As String
SomeString = "I am studying at University of Texas at Arlington"

Debug.Print StringContainsAny(SomeString, False, "university of texas") 'True
Debug.Print StringContainsAny(SomeString, True, "university of texas")  'False
Debug.Print StringContainsAny(SomeString, True, "University of Texas")  'True

But also:

Debug.Print StringContainsAny(SomeString, False, "TEXAS", "SMURF") 'True

And:

Debug.Print StringContainsAny(SomeString, False, "univ", "of", "tex") 'True

Or:

Dim Words As String()
Words = Split("univ of tex", " ")
Debug.Print StringContainsAny(SomeString, False, Words) 'True

You can specify as many values as you need for find_values(), and the function exits on the first match found.

If you specifically want to return true when all parts of "Univ of Tex" are found in the string, you'd have to tweak the code a bit, to return true when all passed arguments are found in the string. But then perhaps the method should be renamed StringContainsAll - perhaps something like this:

Public Function StringContainsAll(string_source As String, _
                                  ByVal caseSensitive As Boolean, _
                                  ParamArray find_values()) As Boolean

    Dim i As Integer, found As Boolean

    If caseSensitive Then

        For i = LBound(find_values) To UBound(find_values)
            found = (InStr(1, string_source, find_values(i), vbBinaryCompare) <> 0)
            If Not found Then Exit For
        Next

    Else

        For i = LBound(find_values) To UBound(find_values)
            found = (InStr(1, LCase$(string_source), _
                              LCase$(find_values(i)), vbBinaryCompare) <> 0)
            If Not found Then Exit For
        Next

    End If

    StringContainsAll = found

End Function

Note that this last snippet isn't tested and doesn't care in what order the words are found in the string.