Select Case Statements
Another statement available in VBA for conditional processing is the Select Case statement. If you have a variable and you want different actions to occur depending on the value of that variable, you can use a series of If statements as follows:
If x=1 then MsgBox "x=1" If x=2 then MsgBox "x=2" If x=3 then MsgBox "x=3"
However, this is a good example of where a Select Case statement makes the code much cleaner:
x = 23
Select Case (x)
Case (1)
MsgBox "x=1"
Case (23)
MsgBox "x=23"
End Select
The Select Case statement provides a simple means to interrogate a specified variable and take action accordingly. The statement Select Case (x) defines the variable to be interrogated as x and is the start of the block of code for this procedure. Case (1) gives the action for if the value is 1-show a message box showing "x=1." Case (23) gives the action for if the value is 23-show a message box showing "x=23." Because x has been set to 23 at the start of the code, the message box will show "x=23."
You can also include the statements To and Is in the Case statement:
Sub Test_Case (Grade)
Select Case Grade
Case 1
Msgbox "Grade 1"
Case 2, 3
Msgbox "Grade 2 or 3"
Case 4 To 6
Msgbox "Grade 4, 5 or 6"
Case Is > 8
MsgBox "Grade is above 8"
Case Else
Msgbox "Grade not in conditional statements"
End Select
End Sub
