2.3.2 Control Structures: Select Case

The Select Case statement in VBA is another form of conditional branching, used to execute different blocks of code based on the value of a variable or expression. It's often more efficient and readable than using multiple If...Then...Else statements, especially when checking a single expression against many possible values.

1. Syntax of Select Case

The basic syntax of the Select Case statement is:

Select Case expression
    Case value1
        ' Code to execute if expression = value1
    Case value2
        ' Code to execute if expression = value2
    Case Else
        ' Code to execute if expression doesn't match any Case
End Select

• expression: The variable or value you are testing.
• value1, value2, ...: The values that you compare against the expression.
• Case Else: An optional part where you specify code to run if none of the other cases match. It works like the Else part of an If...Then statement.

2. Simple Example

Here’s an example that uses Select Case to evaluate a variable and execute different code depending on its value:

Dim dayOfWeek As Integer
dayOfWeek = 3 ' Assume 1=Monday, 2=Tuesday, ..., 7=Sunday

Select Case dayOfWeek
    Case 1
        MsgBox "Today is Monday."
    Case 2
        MsgBox "Today is Tuesday."
    Case 3
        MsgBox "Today is Wednesday."
    Case Else
        MsgBox "It's the weekend or another day."
End Select

In this example, dayOfWeek is compared with several values (1, 2, 3) to determine which day it corresponds to. If none of the specified values match, the code in the Case Else block is executed.

3. Multiple Values in a Single Case

You can test multiple values in a single Case using commas. This is useful when several values should trigger the same block of code.

Example:

Dim grade As String
grade = "A"

Select Case grade
    Case "A", "B"
        MsgBox "You passed with a good grade!"
    Case "C"
        MsgBox "You passed."
    Case Else
        MsgBox "You did not pass."
End Select

In this example, if grade is either "A" or "B", the same message ("You passed with a good grade!") is displayed.

4. Using Ranges in Select Case

You can also use ranges in Select Case to check whether an expression falls within a specific range of values. This is done using the To keyword.

Example:

Dim score As Integer
score = 85

Select Case score
    Case 90 To 100
        MsgBox "Grade: A"
    Case 80 To 89
        MsgBox "Grade: B"
    Case 70 To 79
        MsgBox "Grade: C"
    Case Else
        MsgBox "Grade: F"
End Select

In this example, if the score is between 90 and 100, it will display "Grade: A". The Case 90 To 100 statement is a shorthand for checking if the value falls within that range.

5. Using Expressions in Select Case

You can also use expressions within Select Case. This allows you to evaluate the result of a function or calculation directly.

Example:

Dim number As Integer
number = 7

Select Case True
    Case (number < 5)
        MsgBox "Number is less than 5."
    Case (number >= 5 And number <= 10)
        MsgBox "Number is between 5 and 10."
    Case Else
        MsgBox "Number is greater than 10."
End Select

In this example, the Select Case is comparing the result of expressions instead of a direct variable comparison. The True keyword is used so that each case evaluates a condition, and the first condition that is true will execute its corresponding block of code.

6. Case Else

The Case Else block is optional but useful for handling situations where none of the specified cases match the expression. It works as a fallback option.

Example:

Dim fruit As String
fruit = "Apple"

Select Case fruit
    Case "Banana"
        MsgBox "You have a Banana."
    Case "Orange"
        MsgBox "You have an Orange."
    Case Else
        MsgBox "You have some other fruit."
End Select

In this case, if the variable fruit is neither "Banana" nor "Orange", the code in the Case Else block will execute.

7. Efficiency of Select Case

• Readability: Select Case is often easier to read and understand compared to multiple If...Then...Else statements, especially when dealing with many different values.
• Performance: In cases with many conditions, Select Case can be more efficient than multiple If...Then...Else statements because it performs direct value comparisons in a structured way.

8. Conclusion

The Select Case statement is a powerful tool for branching logic, especially when you need to evaluate a single expression against multiple potential values. By using Select Case, you can streamline your decision-making process in VBA, improve code readability, and ensure your program runs efficiently when handling multiple conditions.