vbaexcelexcel-2013

VBA: Passing multiple values to Instr


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?


Solution

  • [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