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