VBA: Passing multiple values to Instr

Question

Right now I have a long list of "Industry" values that users have submitted through a form. I've written a macro that will search for particular terms in those values and paste values that conform to my significantly smaller list of "acceptable" Industry values. The point is to reclassify all of the current industry values users have submitted into my existing taxonomy. Here's the way my If-Then statements look as of right now:

If InStr(1, Cells(i, "A").Value, "Energy") > 0 Or InStr(1, Cells(i, "A").Value, "Electricity") > 0 Or InStr(1, Cells(i, "A").Value, "Gas") > 0 Or InStr(1, Cells(i, "A").Value, "Utilit") > 0 Then
Cells(i, "B").Value = "Energy & Utilities"
End If

It's pretty ugly. Works perfectly fine, but is a bear to read. My macro is littered with these barely-intelligible If-Then statements, and it's difficult to read through it all. Is there any way to pass multiple values to the "String2" argument of Instr?

Answer 1

If Instr(1, Cells(i, "A").Value, "EnergyElectricityGasUtilit") > 0 Then
    Cells(i, "B").Value = "Energy & Utilities"

Answer 2

There is no way to pass multiple parameters to InStr, but you could create your own sub that handles it. I would create a sub that takes the string to look in and an array of strings to search for. It could then loop over the array and call InStr for each string. Have it return a final result of whether it found all of them or not, and then replace all of your ugly conditional statements with one call to your sub.

Answer 3

[On Edit: I modified the function so that if the last argument is an integer it plays the role of the compareMode parameter in Instr (0 for case-sensitive search, which is the default, and 1 for case-insensitive). As a final tweak, you could also pass a Boolean rather than an integer for the optional final argument with True corresponding to case-insensitive and False corresponding to the default case-sensitive]

If you do this sort of thing a lot it makes sense to write a function which is similar to inStr but can handle multiple patterns. ParamArray is a natural tool to use:

Function Contains(str As String, ParamArray args()) As Boolean
    Dim i As Long, n As Long, mode As Integer

    n = UBound(args)
    If TypeName(args(n)) <> "String" Then
        mode = args(n)
        mode = Abs(mode) 'So True => -1 => 1 for case-insensitive search
        n = n - 1
    End If

    For i = 0 To n
        If InStr(1, str, args(i), mode) > 0 Then
            Contains = True
            Exit Function
        End If
    Next i
    Contains = False
End Function

Tested like:

Sub Test()
    Debug.Print Contains("General Electric", "Gas", "Electric", "Oil")
    Debug.Print Contains("General electric", "Gas", "Electric", "Oil")
    Debug.Print Contains("General electric", "Gas", "Electric", "Oil", False)
    Debug.Print Contains("General electric", "Gas", "Electric", "Oil", True)
    Debug.Print Contains("General electric", "Gas", "Electric", "Oil", 1)
    Debug.Print Contains("General Motors", "Gas", "Electric", "Oil")
End Sub

Output:

True
False
False
True
True
False