The VBA select case statement

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, 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 example in the coming section of this tutorial. I will also show you using 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.

Where:

• expression_to_test after the Select keyword is required. For example, DayNumber.
• This is followed by the Case keyword and expressionlist. If 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 examples below.
• If none of the Case matches the testexpression the optional Else part executes.

A simple example of using Select Case VBA

Let me start with a simple example for understanding how 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.

 

 

You see, the value of the variable is 30 that matched the third case.

An example of using ‘To’ keyword to define boundaries

The “To” keyword can be used in Case statement for defining the boundaries of range to test for expressiontest. In that case, the first value must be less than or equal to the second value. See the following example:

The result:

In the code, you can see how ‘To’ keyword is used. As variable marks value is = 85, so it matched 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 expresssionList. However, it must be convertible to the testExpression data type. In 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:

As there is no possibility of a day other than used in the Case statements so 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 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:

You can see, an ActiveX button is added to the excel sheet. As you click the button, the code behind it will check the value of excel sheet A1 cell. If this is White, Cyan or Dark Blue, the B1 column will be updated by Black value.

Similarly, if 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 ‘Is’ keyword in the Select Case statement. You may use ‘Is’ keyword with the comparison operator like =, >=, <= etc.

See an example below:

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.