Using Select Case Statements in VBA

Select case statements are sequences of VBA statements that behave similar to an If-ElseIf-Else statement. While they are similar in terms of functionality, they can provide clearer and more readable code.

Overview

This post within the Getting Started with VBA series finishes the focus on Logic in VBA.

A Select Case statement (sometimes referred to as a switch statement, or case switch statement) is another control flow mechanism which permits the execution of certain blocks of code. While the capabilities of a Select Case statement are similar to those of the If-ElseIf-Else statement, they are limited in terms of the complexity of tests. Regardless, the Select Case statement’s ease of readability and cleanliness make a great alternative for simple tests.

A Simple Select Case Statement

Typically, a Select Case statement will test a variable against any number of string or numeric literals. A Select Case statement begins with the declaration of a test expression, which is a variable to be tested against the string liberals. The following example contains simple case statements which assigns a letter grade based on a numeric score.

In the above example, the variable Score is the test expression. This variable is tested against each Case statement through the initial Select statement declaration line (line 5). If no values return true, the final Else statement will execute.

Upon executing a single statement, the control flow then completely exits the switch statement (e.g. the score of 85 is True for the 2nd, 3rd, 4th, and 5th case statement, but only executes the first “True” statement it encounters).

A Testing Multiple Values in One Case

A useful approach when many string values will return the same result, a single case statement can test a variable against multiple string literals. This works similar to an Or operator within an If statement.

In this example, if the variable is assigned the grade A, B, C, or D, the statement assigns “Pass” to variable Status. Otherwise, “Fail” is assigned.

Testing Multiple Numbers in One Case

While multiple numbers can be placed in an individual case statement, it’s important to note that there may be unintended results when using the greater/less than operators to test a number within a range. For example, if you want to test a number between 100 and 60, the following will NOT work:

Given that multiple values within a case statement mimic the Or operator, this statement effectively says “Assign variable Status the value ‘Pass’ if Score is less than 100 OR greater than 60″. With 40 being less than 100, the value “True” will be returned as only one of the tests needs to return “True” when an Or operator is in use.

To test if a number lies between a numeric range, use the To operator.

The above will return the message “Fail” as 40 is not between 60 and 100.

Nested Select Case Statements

Select Case statements can be nested within each other. This is effective for items with hierarchical and categorical structures.

In the following example, a department store has three primary departments: Electronics, Appliances, and Clothing. Each department has three types of employees: Managers, Supervisors, and Clerks. Using a nested case statement, we can lookup the department and position using department and position codes.

The above script will return the message “Supervisor is in the Electronics Department”.

Other Notes

  • Case statements are useful for situations where there are always a set number of values to test against a variable.
  • All functions of case select statements can be accomplished through traditional If-ElseIf-Else statements. At times, Case statements may be the preferred alternative in terms of readability and simplicity.
  • In C-based languages, Select Case Statements will test each statement instead of exiting after the first “True” statement. This has a similar affect to consecutive If statements.

Leave a Reply