Contents
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:
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 SelectHere 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.
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:
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 SubIn 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:
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 SubIn 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
Access
Power BI
