VBA Select Case Statement

The Select Case is useful for scenarios where you have a single expression to evaluate that has several possible values. Learn with demos!

VBA Select Case tutorial image illustrating the usage of the Select Case statement for efficient decision-making in Visual Basic for Applications programming.

The Select Case is another decision-making statement in VBA that may be useful for scenarios where you have a single expression to evaluate that has several possible values.

  • For example, you require executing a block of code based on the day of the Week.
  • In that case, you may get the current day from the system, and assign it to a variable.
  • Use this variable as an expression in the Select VBA statement.
  • Now this expression can hold seven possible values – Monday to Saturday. Each day value can be tested in the Case statement with its block of statement to execute.

I will show you such an example in the coming section of this tutorial. I will also show you how to use Select Case with Microsoft Excel worksheet, first the syntax of using Select statement.

Syntax of using Select Case statement

Following is the general structure of using the Select case in VBA.

Select [ Case ] expression_to_test 

    [ Case expression_list1 

        [ statements / block of code to execute ] ] 

    [ Case expression_list2 

        [ statements / block of code to execute ] ] 

    [ Case Else 

        [ else statements / block of code to execute] ] 

End Select

Where:

Part Description
expression_to_test expression_to_test after the Select keyword is required. For example, DayNumber.
expression_list
  • This is followed by the Case keyword and expression_list.
  • If the expression in the Case matches the testexpression, the optional statements inside that case will execute.
  • The expression can be used in a variety of ways like testing a single value or range of values etc.
  • This will be clear in the examples below.
Else If none of the Cases matches the test expression, the optional Else part executes.

A simple example of using Select Case VBA

Let me start with a simple example for understanding how the Select Case statement works in VBA (before doing this in Excel).

  • An Integer type variable is declared with an initial value.
  • That variable is used in the Select Case and three cases are used to test its value.
  • A message box statement is executed for the matched case.
  • In the end, the Else statement is also used.
Private Sub Sel_case_exmample()

'Example of using Select Case
   Dim intA As Integer
   intA = 30

   Select Case intA
      Case 10

         MsgBox "First Case is matched!"

      Case 20

         MsgBox "The Second Case is matched!"

      Case 30

         MsgBox "Third Case is matched in Select Case!"

      Case Else

         MsgBox "None of the Case is matched!"

   End Select

End Sub

VBA Select Case

 

 

You see, the value of the variable is 30 which matches the third case.

An example of using 'To' keyword to define boundaries

The “To” keyword can be used in the Case statement for defining the boundaries of range to test for expression_to_test.

In that case, the first value must be less than or equal to the second value. See the following example:

Private Sub Sel_case_exmample()

'Example of using Select Case with 'To' Keyword

   Dim marks As Integer

   marks = InputBox("Enter mark fro 1 to 100?")



   Select Case marks

      Case 1 To 32

         MsgBox "Fail!"

      Case 33 To 59

         MsgBox "F Grade"

      Case 60 To 70

         MsgBox "D Grade"

      Case 71 To 80

         MsgBox "C Grade"

      Case 81 To 90

         MsgBox "B Grade"

      Case 91 To 95

         MsgBox "A Grade"

      Case 96 To 100

         MsgBox "A+ Grade"

      Case Else

         MsgBox "Out of range"

   End Select

End Sub

The result:

Select Case To

In the code, you can see how the ‘To’ keyword is used. As the value of the variable mark is = 85, it matches the following case:

Case 81 To 90

MsgBox "B Grade"

Try entering different values in the input text field and see the output yourself.

Using String data type in Select Case

You may use any data type in the expresssion_List. However, it must be convertible to the expression_to_test data type.

In the above two examples, I used Integer type and cases also used numbers.

In the following example, the current day name is taken from the system and assigned to a String variable.

That string variable is used in the Select Case statement while subsequent cases are tested with the day names as shown below:

Private Sub Sel_case_exmample()

'Example of using Select Case with 'To' Keyword

Dim sDayName As String

sDayName = Format(Date, "dddd")

Select Case sDayName

    Case "Saturday"

        MsgBox "Saturday, Its Weekend dude!"

    Case "Sunday"

        MsgBox "Yay, Its Sunday!"

    Case "Monday"

        MsgBox "mmm, Monday Blues!"

    Case "Tuesday"

        MsgBox "Its Tuesday, lets do some work!"

    Case "Wednesday"

        MsgBox "Wed, Ok keep on working!"

    Case "Thursday"

        MsgBox "Thursday, Well closing the work!"

    Case "Friday"

        MsgBox "We are so close, Its Friday!"

End Select

End Sub

Select Case String

As there is no possibility of a day other than used in the Case statements, no Else block was used.

Example of Excel VBA Select with multiple values

For this example, two more things are done.

  • One, the Excel cells are used in the Select Case.
  • The other is using multiple values to check that are separated by commas in the Case statement.
  • So, it serves the kind of ‘Or’ operator purpose in the If statement.

The Excel workbook’s column’s A1 value is assigned to a variable. This variable is used in the Select Case as testExpression.

For the matched case, the B2 cell will be updated as follows:

Private Sub select_case_Click()

Dim color As String



color = Range("A1").Value



Select Case color

    Case "White", "Cyan", "Dark Blue"

        Range("B1").Value = "Black"

    Case "Green", "Black", "Brown"

        Range("B1").Value = "Yellow"

    Case "Blue", "Sky Blue"

        Range("B1").Value = "Maroon"

    Case Else

    Range("B1").Value = "Pink"

End Select

End Sub

Select Case excel

You can see that an ActiveX button is added to the Excel sheet. As you click the button, the code behind will check the value of the Excel sheet A1 cell.

If this is White, Cyan, or Dark Blue, the B1 column will be updated by Black value.

Similarly, if the A1 value is changed to Green, Black, or Brown, the B2 value will be set as Yellow.

You can see, how multiple color names (Strings) are separated in the Case statements by using commas.

A Demo of using ‘Is’ operator in Select Case

You may also use the ‘Is’ keyword in the Select Case statement.

You may use ‘Is’ keyword with the comparison operator like =, >=, <=, etc.

See an example below:

Private Sub select_case_Click()

Dim num As String



num = Range("A1").Value



Select Case num

    Case Is = 1, 3, 5, 7, 9, 11

        Range("B1").Value = "ODD"

    Case Is = 2, 3, 6, 8, 10, 12

        Range("B1").Value = "Even"

End Select

End Sub

Select Case excel Is

Enter the value from 1-12 in the A1 cell and press the button to see how it updates the B1 cell by Even or ODD text.