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