Switch Statement

VBA Switch Statement

The Select Case statement is a powerful control flow structure in VBA that allows you to execute different code blocks based on the value of a specific expression. It’s often used as an alternative to nested If...Then...Else statements, especially when you need to compare a single expression against multiple values.

How it Works:

Expression Evaluation: The expression is evaluated first.
Case Matching: The value of the expression is compared to each Case statement.
Code Execution: If a match is found, the corresponding code block is executed.
Default Case: If no match is found, the code in the Case Else block is executed (optional).

Syntax:

Select Case expression
Case value1
‘ Code to execute if expression equals value1
Case value2, value3
‘ Code to execute if expression equals value2 or value3
Case Else
‘ Code to execute if expression doesn’t match any of the cases
End Select

Example

				
					Sub CheckGrade()
    Dim grade As String
    grade = "B"

    Select Case grade
        Case "A"
            MsgBox "Excellent!"
        Case "B", "C"
            MsgBox "Good job!"
        Case "D", "F"
            MsgBox "Needs improvement."
        Case Else
            MsgBox "Invalid grade."
    End Select
End Sub

Key Points:

The Select Case statement is often more readable and efficient than multiple nested If...Then...Else statements.
You can use range expressions in the Case statements to match multiple values.
The Case Else block is optional and provides a default action if no match is found.
You can use logical operators within the Case statements to create more complex conditions.