In VBA (Visual Basic for Applications), the Select Case statement is a powerful and more readable alternative to using multiple If…ElseIf…Else statements. It allows you to execute different blocks of code based on the value of an expression. It’s especially useful when you have several conditions to check.

Here’s how to use the Select Case statement:

Basic Syntax

Select Case testexpression
    Case expression1
        ' Code to run when testexpression matches expression1
    Case expression2
        ' Code to run when testexpression matches expression2
    ' ...
    Case Else
        ' Code to run if testexpression does not match any above cases
End Select

testexpression is evaluated once, and its value is compared with the values specified in the Case statements.

Example Usage

Imagine you want to display a message based on the value of a variable dayOfWeek:

Dim dayOfWeek As Integer
dayOfWeek = 3 ' For example, 3 represents Wednesday

Select Case dayOfWeek
    Case 1
        MsgBox "Monday"
    Case 2
        MsgBox "Tuesday"
    Case 3
        MsgBox "Wednesday"
    Case 4
        MsgBox "Thursday"
    Case 5
        MsgBox "Friday"
    Case 6
        MsgBox "Saturday"
    Case 7
        MsgBox "Sunday"
    Case Else
        MsgBox "Invalid day"
End Select

This code will display “Wednesday” in a message box.

Using Multiple Values in a Single Case

You can specify multiple values for a single Case separated by commas:

Select Case score
    Case 90 To 100
        grade = "A"
    Case 80 To 89
        grade = "B"
    Case 70 To 79
        grade = "C"
    Case Else
        grade = "F"
End Select

This code assigns a letter grade based on a score.

Using Range of Values

You can also use a range of values in a Case statement using To:

Select Case temperature
    Case -273 To 0
        MsgBox "Below freezing"
    Case 1 To 100
        MsgBox "Above freezing and below boiling"
    Case 101 To 1000
        MsgBox "Above boiling"
    Case Else
        MsgBox "Invalid temperature"
End Select

Using Expressions

Case statements can also include expressions:

Select Case age
    Case Is < 13
        category = "Child"
    Case Is < 20
        category = "Teenager"
    Case Is < 65
        category = "Adult"
    Case Else
        category = "Senior"
End Select

Nested Select Case

You can nest Select Case statements within each other for more complex decision-making processes.

Performance Consideration

Select Case is generally more efficient and readable than a series of If…ElseIf statements, especially when dealing with a long list of conditions.

Select Case is ideal for making your VBA code cleaner, more readable, and easier to maintain, particularly when you’re checking a single expression against multiple potential values.

or variable. You can use it to make more complex and structured decisions in your Excel macros or other VBA programs

Switch the language

Follow us on Social Media

facebooklinkedin

Unlock Your Potential

Excel

Access

Power BI

Help us grow the project