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