嵌套If-ElseIf-Else结构抛出Expected End错误，请求排查修复

Hey there! That Expected End error is popping up because your nested If statements aren't properly closed with matching End If statements. Let's walk through fixing this, plus clean up the code to make it easier to maintain.

What's Causing the Error? #

Looking at your code snippet:

• The first outer If block (boolAS1 = True And boolAS2 = True) has nested If statements but no closing End If lines before moving to the ElseIf block.
• The ElseIf block is also cut off mid-logic, missing both closing End If statements and the rest of its conditional flow.

Corrected Full Code #

Here's the fixed version with all missing End If statements added, plus minor simplifications:

Function getArrayOpt(ByVal cb1, ByVal cb2)
    If boolAS1 And boolAS2 Then ' Simplified boolean check (no need for = True)
        If mon1 = "D" Then
            getArrayOpt = Array(cb1, cb2)
        Else
            If mon2 = "S" Then
                getArrayOpt = Array(cb1, cb2, vbNullString)
            Else
                getArrayOpt = Array(cb1, cb2, st(0), st(1))
            End If ' Closes the mon2 = "S" If
        End If ' Closes the mon1 = "D" If
    ElseIf boolAS1 And Not boolAS2 Then
        If mon1 = "D" Then
            getArrayOpt = Array(cb1, cb2)
        Else
            If mon2 = "S" Then
                getArrayOpt = Array(cb1, vbNullString)
            Else
                getArrayOpt = Array(cb1, cb2, st(0))
            End If ' Closes the mon2 = "S" If
        End If ' Closes the mon1 = "D" If
    ' Optional: Add an Else block to handle cases where boolAS1 is False
    ' Else
    '     getArrayOpt = Array() ' Set a default return value here
    End If ' Closes the outer If/ElseIf block
End Function

Key Fixes & Improvements #

• Added all missing End If statements to match every If opening (VBA requires strict pairing of these).
• Simplified boolean checks like boolAS1 = True to just boolAS1 (VBA evaluates boolean variables directly, making code cleaner).
• Added a comment for an optional Else block to cover scenarios where boolAS1 is False (your original code didn't address this, but it's a good practice to handle all cases).

Bonus: Optimize for Readability #

Nested If statements can get messy fast. Here's a refactored version using Select Case to flatten the logic and reduce future errors:

Function getArrayOpt(ByVal cb1, ByVal cb2)
    ' Handle boolAS1 + boolAS2 = True scenario
    If boolAS1 And boolAS2 Then
        Select Case mon1
            Case "D"
                getArrayOpt = Array(cb1, cb2)
            Case Else
                Select Case mon2
                    Case "S"
                        getArrayOpt = Array(cb1, cb2, vbNullString)
                    Case Else
                        getArrayOpt = Array(cb1, cb2, st(0), st(1))
                End Select
        End Select
    ' Handle boolAS1 = True, boolAS2 = False scenario
    ElseIf boolAS1 And Not boolAS2 Then
        Select Case mon1
            Case "D"
                getArrayOpt = Array(cb1, cb2)
            Case Else
                Select Case mon2
                    Case "S"
                        getArrayOpt = Array(cb1, vbNullString)
                    Case Else
                        getArrayOpt = Array(cb1, cb2, st(0))
                End Select
        End Select
    ' Default case for all other scenarios
    Else
        getArrayOpt = Array(vbNullString) ' Adjust this default as needed
    End If
End Function

Select Case makes the conditional flow easier to scan and reduces the chance of forgetting closing statements.

内容的提问来源于stack exchange，提问作者Cauan Martins