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
VBA
testexpression is evaluated once, and its value is compared with the values specified in the Case statements.
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 SelectExample Usage
Imagine you want to display a message based on the value of a variable dayOfWeek:VBA
This code will display “Wednesday” in a message box.
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 SelectUsing Multiple Values in a Single Case
You can specify multiple values for a single Case separated by commas:VBA
This code assigns a letter grade based on a score.
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 SelectUsing Range of Values
You can also use a range of values in a Case statement using To:VBA
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 SelectUsing Expressions
Case statements can also include expressions:VBA
Select Case age
Case Is < 13
category = "Child"
Case Is < 20
category = "Teenager"
Case Is < 65
category = "Adult"
Case Else
category = "Senior"
End SelectNested 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 programsSwitch the language
Unlock Your Potential
Excel
Access
Power BI
