How to use the Select Case statement in VBA?

The Select Case statement in VBA is used to make decisions based on the value of an expression or variable. It is an alternative to the If…Then…Else statement when you have multiple conditions to evaluate. You can use Select Case like this:

VBA
Select Case expression
    Case value1
        ' Code to execute if the expression is equal to value1
    Case value2
        ' Code to execute if the expression is equal to value2
    Case value3
        ' Code to execute if the expression is equal to value3
    Case Else
        ' Code to execute if the expression doesn't match any of the above values
End Select

Here are some key aspects:

  • Expression: It can be a variable, a constant or an expression that you want to evaluate.
  • Case: Each Case followed by a value (or an expression) represents a condition that is evaluated to determine which block of code will be executed.
  • Else: The Case Else block is optional and will be executed if none of the above conditions are met.
  • Value: It can be a constant value, a variable or even an expression. When the expression matches one of the values within the Case, the code corresponding to that case is executed.
VBA
Sub ExampleSelectCase()
    Dim number As Integer
    number = 3

    Select Case number
        Case 1
            MsgBox "The number is equal to 1"
        Case 2
            MsgBox "The number is equal to 2"
        Case 3
            MsgBox "The number is equal to 3"
        Case Else
            MsgBox "The number doesn't match any of the above options"
    End Select
End Sub

Examples

Select Case

Suppose you want to write a program that displays a message depending on the time of day. Here is an example:

VBA
Sub GreetingBasedOnTime()
    Dim hour As Integer
    hour = Hour(Now) ' Gets the current system time

    Select Case hour
        Case 0 To 11
            MsgBox "Good morning!"
        Case 12 To 17
            MsgBox "Good afternoon!"
        Case 18 To 23
            MsgBox "Good evening!"
        Case Else
            MsgBox "Invalid time"
    End Select
End Sub

In this example, time is compared to several value ranges, and based on the current time, a corresponding greeting message will be displayed.

Nested Select Case

You can nest multiple Select Case statements to handle more complex decisions. Here is an example:

VBA
Sub GradeExam()
    Dim score As Integer
    score = 75 ' Exam score

    Select Case score
        Case Is >= 90
            MsgBox "Excellent"
        Case 80 To 89
            MsgBox "Good job"
        Case 70 To 79
            MsgBox "Satisfactory"
        Case Is < 70
            Select Case score
                Case 60 To 69
                    MsgBox "Needs improvement"
                Case Is < 60
                    MsgBox "Failed"
            End Select
        Case Else
            MsgBox "Invalid score"
    End Select
End Sub

In this example, a nested Select Case is used to evaluate different ranges of scores and give a grade. If the score is less than 70, another Select Case within the Case Is < 70 is used to provide a more specific rating.

In summary, the Select Case statement in VBA is a powerful way to handle multiple conditions based on the value of an expression or variable. You can use it to make more complex and structured decisions in your Excel macros or other VBA programs

Switch the language

Spanish

Help us grow the project

Unlock Your Potential

Excel

Basic - Advanced

Access

Access Basic - Advanced

Power BI

Power BI Basic - Advanced

Join our Facebook Group!