SELECT CASE

In the previous chapter you have learnt IF THEN ELSE. In this chapter you will learn about Select Case.

Select Case is also known as Switch Cases. VBA Select Case can be used in placed of complex Nested If statements. This makes the VBA code faster to execute and easier to understand.

To start select case we use “Select Case” and to end it we use “End Select”. In between we give the condition for different cases.

Below is the example to select the color. If R, G or B is not selected then Case Else will execute.

Sub Check_Selected_Color()

Dim Selected_Color As String
 Selected_Color = "R"

Select Case Selected_Color

Case "R"
     MsgBox "Red"
 Case "G"
     MsgBox "Green"
 Case "B"
     MsgBox "Blue"
 Case Else
     MsgBox "None"
 End Select

End Sub

 

We also can give the value in range. We need to use To or to give the multiple value give it comma separated.

Please see the below example wherein we use first case as “Case 1 to 5” and for second case we are taking as “Case 6, 7, 8”.

 

Sub Check_Numbers()

Dim my_number As Integer

my_number = 8

Select Case my_number
 Case 1 To 5
 MsgBox "Between 1 and 5"
 Case 6, 7, 8
 MsgBox "Between 6 and 8"
 Case 9 To 10
 MsgBox "Equal to 9 or 10"
 Case Else
 MsgBox "Not between 1 and 10"
End Select


End Sub

Next Chapter>>Workbooks