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 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.
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
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 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:
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