How to Search For Multiple Strings Using VBA InStr Function

In the previous post we learnt how to use the InStr function to check whether a string contains a substring. Today I’m going to show you how to search for multiple strings using the VBA InStr function. For an example consider this sample string.

“Learn to develop Excel VBA applications”

Assume you want to check whether this string contains either Excel or VBA. We can’t use the InStr function as it is, to search for multiple values. But we can create our own function using the InStr function to do the job. I’m going to develop two types of solutions to search for multiple values using the VBA InStr function. One function will return true or false depending on whether it can find at least one of the multiple strings or not. And the other function will return the positions of each and every substring. So stay tuned.

Function 1 - Return true or false depending on whether at least one of the multiple values are available or not.

This function needs two input parameters. The string being searched and the multiple strings. Below is the complete code of the first function.

Function MultipleStringsSearch(SText As String, MultipleStrings() As String) As Boolean

     Dim i As Integer

    For i = LBound(MultipleStrings) To UBound(MultipleStrings)
        If Len(MultipleStrings(i)) > 0 Then
            If InStr(1, SText, MultipleStrings(i), vbTextCompare) > 0 Then
                MultipleStringSearch = True
                Exit Function
            End If
        End If
    Next i

End Function

Below is the explanation of the above code.

Here we pass the multiple values to the function as an array. Also this function returns either true or false. So the data type of the returned value is boolean.

Function MultipleStringsSearch(SText As String, MultipleStrings() As String) As Boolean

Then a For Next statement is used to iterate through each element of the MultipleStrings array.

For i = LBound(MultipleStrings) To UBound(MultipleStrings)

Next i

We should check whether the length of each string is higher than 0. Because the InStr function returns the start value when the length of the search string is 0. Click the link below to see more details.

InStr function return Start value when length of second string is 0

If Len(MultipleStrings(i)) > 0 Then

Then the InStr function is used to check whether the SText string contains each substring of the MultipleStrings array.

If InStr(1, SText, MultipleStrings(i), vbTextCompare) > 0 Then

Now let’s see how to call this function in subroutines.

Let’s use a somewhat long string as the first string. This will be the string being searched.

MyString = "When we develop Excel VBA applications sometimes we need the application to check whether some strings are included in other strings."

And the multiple strings we are searching for are “we”, “Excel” and “applications”.

Sub Test_1()

     Dim MyString As String
     Dim StringsArr(2) As String

     MyString = "When we develop Excel VBA applications sometimes we need the application to check whether some strings are included in other strings."

     StringsArr(0) = "we"
     StringsArr(1) = "Excel"
     StringsArr(2) = "applications"

     Debug.Print MultipleStringsSearch(MyString, StringsArr)

End Sub

In this subroutine, a fixed size array is declared to contain the multiple strings.

Dim StringsArr(2) As String

And then added the multiple strings to that array as follows.

StringsArr(0) = "we"
StringsArr(1) = "Excel"
StringsArr(2) = "applications"

Want to learn more about fixed size arrays? Check this post.

Fixed Size Arrays in VBA

This is the output of the Test_1 subroutine.

Here is the second example.

Sub Test_2()

     Dim MyString As String
     Dim StringsArr(2) As String

     MyString = "I went to home"

     StringsArr(0) = "Excel"
     StringsArr(1) = "vba"
     StringsArr(2) = "application"

     Debug.Print MultipleStringsSearch(MyString, StringsArr)

End Sub

As you can see the multiple strings are not found inside the MyString. So the output of the Test_2 subroutine is false.

In the above two examples, multiple strings were added to the StringsArr array one by one. However if you have the multiple values in a one string separated by commas, then you can use Split function to convert it to an array easily. See the below example.

Sub Test_3()

     Dim MyString As String
     Dim MultipleStrings As String
     Dim StringsArr() As String

     MyString = "When we develop Excel VBA applications sometimes we need the application to check whether some strings are included in other strings."

     MultipleStrings = "Excel,vba,application"

     StringsArr = Split(MultipleStrings, ",")

     Debug.Print MultipleStringsSearch(MyString, StringsArr)

End Sub

This is the output of the Test_3 subroutine.

Function 2 - Return the positions of each multiple string

Previous function returned true or false depending on whether the multiple values are available inside the string being searched or not. But this new function returns the position of each multiple string inside the main string. For example if the multiple strings are found inside the main string at the positions x, y and z then the function will return x,y,z in an array.

Function InStrResultForMultipleStrings(SText As String, MultipleStrings() As String) As Variant

     Dim i As Integer
     Dim ResultArr() As Variant
     Dim iStart As Integer
     Dim iEnd As Integer

     iStart = LBound(MultipleStrings)
     iEnd = UBound(MultipleStrings)
     ReDim ResultArr(iStart To iEnd)

     For i = LBound(MultipleStrings) To UBound(MultipleStrings)
         ResultArr(i) = InStr(1, SText, MultipleStrings(i), vbTextCompare)
     Next i

     InStrResultForMultipleStrings = ResultArr()

End Function

Input parameters for this second function are the same as the previous function. However this function returns a value of the variant data type instead of the boolean. Following example shows how to call this function within a subroutine.

Sub Test_4()

     Dim MyString As String
     Dim StringsArr(2) As String
     Dim Result() As Variant

     MyString = "Excel formulas and vba macros"

     StringsArr(0) = "Excel"
     StringsArr(1) = "vba"
     StringsArr(2) = "application"

     Result = InStrResultForMultipleStrings(MyString, StringsArr)

End Sub

Then set a breakpoint at the end of the subroutine and run the macro to see the result in the Locals window.

Word “Excel” is at the position 1 within the MyString and “vba” is at position 20. Also the substring "application" is not available inside the MyString. So the function returns 0 for that substring.

Also note that above two functions carry out a case insensitive search. If you want to do case sensitive search for multiple strings then change the compare type from “vbTextCompare” to “vbBinaryCompare”. So the first function should be changed like this.

Function MultipleStringsSearchCaseSensitive(SText As String, MultipleStrings() As String) As Boolean

     Dim i As Integer

     For i = LBound(MultipleStrings) To UBound(MultipleStrings)
         If Len(MultipleStrings(i)) > 0 Then
             If InStr(1, SText, MultipleStrings(i), vbBinaryCompare) > 0 Then
                 MultipleStringsSearch = True
                 Exit Function
             End If
         End If
     Next i

End Function

You can do the same for the second function as well.