Multiple If/Then Call options-CodePudding

I have several worksheets. There are 3 subroutine CALL options based on the worksheet name. I have tried rearranging and If, If Not, Else and ElseIf, Exit Sub, End If, etc. If the CALL completes and returns, it inevitably gets to the last CALL (here ... IsolOther) and executes it even through the previous CALL worked. I have also tried to figure out the SELECT CASE Function for multiple choices. The CALLs for CBI, Fire, LEA and InCase work perfectly. The problem is when I have a worksheet with a different name ... I want the CALL to go to IsolOther.

        Sub DetermineTabs()

        'DETERMINE WHICH SUBROUTINE TO CALL BASED ON SHEET (TAB) NAME

            Dim newTab As String
            newTab = ActiveSheet.Name

        'Call appropriate Category processing routine    
                    'Call IsolNamesOnly Subroutine if ActiveSheet is named ... CBI
                If newTab = "CBI" Then Call IsolNamesOnly Else
                    'Call IsolNamesOnly Subroutine if ActiveSheet is named ... Fire
                If newTab = "Fire" Then Call IsolNamesOnly Else
                    'Call IsolNamesOnly Subroutine if ActiveSheet is named ... LEA
                If newTab = "LEA" Then Call IsolNamesOnly Else
                    'Call IsolInCase Subroutine if ActiveSheet is named ... InCase
                If newTab = "InCase" Then Call IsolInCase Else
                   'Call IsolOther Subroutine if ActiveSheet is named ... anything else
                If newTab <> "CBI" Or newTab <> "Fire" Or newTab <> "LEA" Or newTab <> "InCase" Then Call IsolOther Else
        
        End Sub

I have also tried "If Not newTab = "CBI" Or newTab = "Fire" Or newTab = "LEA" Or newTab = "InCase" Then Call IsolOther Else" (without quotes as an alternative to last IF line), as well as making the last line individual If Not/Else, but I am failing. If none of the first 4 sheets are found, I want to execute that CALL and End Sub. I have reviewed questions 60781118, 51274411, 24684092, and other Google queries. Help . . . please.

CodePudding user response：

Consider SELECT...CASE and combine multiple conditions with Case Else condition.

Select Case newTab
    Case "CBI", "LEA", "Fire"
    Call IsolNamesOnly 

    Case "InCase" 
    Call IsolInCase 

    Case Else
    Call IsolOther
End Select

CodePudding user response：

`Select Case` vs `If...Then...Else`

The Call statement is considered deprecated.
Select Case statement
If...Then...Else statement

As an alternative to using the Select Case statement (the way to go in this case), you could consider using the If...Then...Else statement.

If you write it in the following (popular) way,...

Sub DetermineTabsIfThenElse()
    
    Dim NewTab As String: NewTab = ActiveSheet.Name
    
    If NewTab = "CBI" Or NewTab = "Fire" Or NewTab = "LEA" Then
        IsolNamesOnly
    ElseIf NewTab = "InCase" Then
        IsolInCase
    Else
        IsolOther
    End If

End Sub

... even if the sheet name is CBI, the strings Fire and LEA will also be evaluated, while in the Select Case version, the statement would be exited immediately.

To mimick the Select Case version, you could rewrite the code in the following way...

Sub DetermineTabsIfThenElseSelectCase()
    
    Dim NewTab As String: NewTab = ActiveSheet.Name
    
    If NewTab = "CBI" Then
        IsolNamesOnly
    ElseIf NewTab = "Fire" Then
        IsolNamesOnly
    ElseIf NewTab = "LEA" Then
        IsolNamesOnly
    ElseIf NewTab = "InCase" Then
        IsolInCase
    Else
        IsolOther
    End If

End Sub

...which would make it even longer (more 'repeated' typing) and less readable.

In practice, I've never noticed any difference in the execution time of any of the versions though.

To prove the previous 'evaluating' point made, replace e.g. "LEA" with a number, make the CBI sheet active and run each of the codes. The first If version will raise a type-mismatch error because NewTab is declared as a string, while the other two versions will not 'notice' it.

Select Case vs If...Then...Else

`Select Case` vs `If...Then...Else`