Below Excel VBA function is only working for first IF condition, ELSEIF is not working

Question

Below Excel VBA function is only working for first If condition, ElseIf is not working.

Function Alarms(L2) As String
    Set a = Application.WorksheetFunction

    If IsError(a.Find("HHH", L2, 1)) = False Then 
        Alarms = "HHH HTRP"
    ElseIf IsError(a.Find(" HI", L2, 1)) <> True Then   
        Alarms = "HI"
    ElseIf IsError(a.Find("IOP", L2, 1)) = False Then   
        Alarms = "IOP"
    ElseIf IsError(a.Find("OOP", L2, 1)) = False Then    
        Alarms = "OOP"
    ElseIf IsError(a.Find("ANS", L2, 1)) = False Then    
        Alarms = "ANS"
    ElseIf IsError(a.Find(" HH", L2, 1)) = False Then    
        Alarms = "HH"
    ElseIf IsError(a.Find("HTRP", L2, 1)) = False Then    
        Alarms = "HHH HTRP"
    ElseIf IsError(a.Find(" LO", L2, 1)) = False Then    
        Alarms = "LO"
    ElseIf IsError(a.Find(" LL", L2, 1)) = False Then    
        Alarms = "LL"
    ElseIf IsError(a.Find("LLL", L2, 1)) = False Then    
        Alarms = "LLL LTRP"
    ElseIf IsError(a.Find("LTRP", L2, 1)) = False Then    
        Alarms = "LLL LTRP"
    ElseIf IsError(a.Find("NR", L2, 1)) = False Then    
        Alarms = "NR"
    ElseIf IsError(a.Find("DV", L2, 1)) = False Then    
        Alarms = "DV"
    ElseIf IsError(a.Find("VEL", L2, 1)) = False Then    
        Alarms = "VEL"
    ElseIf IsError(a.Find("TRIP", L2, 1)) = False Then    
        Alarms = "TRIP"
    ElseIf IsError(a.Find("MHI", L2, 1)) = False Then    
        Alarms = "MHI MLO"
    ElseIf IsError(a.Find("MLO", L2, 1)) = False Then    
        Alarms = "MHI MLO"
    ElseIf IsError(a.Find("CERR", L2, 1)) = False Then    
        Alarms = "CERR"
    ElseIf IsError(a.Find("PERR", L2, 1)) = False Then    
        Alarms = "PERR"
    ElseIf IsError(a.Find("OVR", L2, 1)) = False Then   
        Alarms = "OVR"
    ElseIf IsError(a.Find("FAULT", L2, 1)) = False Then    
        Alarms = "FAULT"
    ElseIf IsError(a.Find("INT", L2, 1)) = False Then    
        Alarms = "INT"
    ElseIf IsError(a.Find("ALM", L2, 1)) = False Then    
        Alarms = "ANN"
    Else
        Alarms = "-"
    End If
End Function

Don't use `WorksheetFunction`... Use [`InStr`](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/instr-function) and test if the result is `> 0`. Also, it always helps to create a [reprex]. — BigBen, Jul 26 '21 at 13:50
Whenever you find yourself using a long chain of `If...ElseIf...` statements that differ only in one of the parameters, there's almost always a better way of doing it. You could iterate over an array of those strings, for example. — jsheeran, Jul 26 '21 at 13:54
Is this a UDF? If yes, I am guessing calling `WorksheetFunction.Find()` is disallowed in UDFs — JAlex, Jul 26 '21 at 14:01

Darren Bartrup-Cook · Accepted Answer · 2021-07-26T14:51:10.373

Select Case would do the trick:

Public Function Alarms(Target As Range) As String

    Select Case True
        Case InStr(Target.Value, "HHH") > 0, InStr(Target.Value, "HTRP") > 0 '<Note: both values checked here.
            Alarms = "HHH HTRP"
        Case InStr(Target.Value, " HI") > 0
            Alarms = "HI"
        Case InStr(Target.Value, "IOP") > 0
            Alarms = "IOP"
        Case InStr(Target.Value, "OOP") > 0
            Alarms = "OOP"
        Case InStr(Target.Value, "ANS") > 0
            Alarms = "ANS"
        Case InStr(Target.Value, " HH") > 0
            Alarms = "HH"
        Case Else
            Alarms = "-"
    End Select

End Function

Note - "OOP" is different from "oop"

Below Excel VBA function is only working for first IF condition, ELSEIF is not working

1 Answers1