How to Use Select Case Statement in VBA

Last updated on 2024-10-12.

Preface

In the northern hemisphere, you might describe December, January and February as the months of winter; March, April, May as those of spring; and so on through the months of the summer and autumn seasons.

In VBA there are functions that can take any date and identify which day-of-the-month, which month or which year that date occupies. Excel too has its DAY, MONTH and YEAR functions.

But if you need to determine which season a date occupies, you'll need some code to do the job. In an Excel formula, you could use three, nested IF functions.

In VBA, you could use If statements in the form If … ElseIf … ElseIf … ElseIf … Else … End If
or in other multiple-If constructions.

But VBA's Select Case statement provides an alternative approach. In adopting it, you may find your code easier to construct, and to maintain, than If-statement coding.

Select Case Construction

This is the syntax of a Select Case statement:

The test-expression can be a single character, a string of characters or a number. Each condition is the value, or set of values, to which you want the statement to respond — by executing the corresponding code.

When the Select Case statement cannot fit the test-expression to any of the Case conditions, the statement's Case Else comes into play.

Note that should more than one condition satisfy the Select Case test expression, then only the first of these will be acted upon.

Caution In most of the statements I’ve been asked to examine, such a possibility was the result of a poorly composed statement that could lead to unanticipated or incorrect results.

Demonstration of Select Case Statement

This is the Select Case applied to associating a date to a season:

For such a simple Select Case statement, you might find it elegant to combine elements of code on the same line, using a : (colon) to separate the elements:

Month(dte) converts the date value supplied as the argument of the function into a numeric value for the Select Case statement to assess.

Case 1, 2, 12 uses commas to separate the discrete numeric values to which the Case responds.

Case 6 To 8 uses the To operator to identify a continuous range of values.

You could use Excel to test the function.

In the fnSeason above, you might agree that the Case Else serves no useful purpose and therefore could be omitted.

It's important always to test your function thoroughly before coming to rely upon it. In haste, in my experience, it's not unlikely that you have the Case conditions incorrect or in the wrong order.

Controlling Program Flow

A common application of the Select Case statement is to give direction to program flow, according to, for example, a user's response to a message questioning their intention.

In the following code the Select Case statement in the cmdDeleteRecord_OnClick event procedure presents the user with this message box.

The Select Case statement then goes on to make Calls to other sub routines according to which button the user clicks to dismiss the message box without any resulting action.

Case Else provides for any action the user may take to dismiss the message box, including clicking the Cancel or the Close button, or pressing Esc on the keyboard.

Use of Comparison Operators

Select Case conditions recognise the comparison operators =, < (less than), >, <> (not equal to), <= (less than or equal to) and >=.

Here is an example of using comparison operators in a function for seasons:

Conditions of Text and Use of Wild cards

The examples above have Case conditions relating to numeric values. A slightly different construction is necessary when your conditions relate to text.

Let's consider this spreadsheet:

This is the function returning the values in Column B:

The Select Case statement tests for truthfulness. That's why each Case condition has to be a logical test that will return either True or False.

Should none of the logical tests prove to be True, the Case Else statement gobbles up all other possibilities.

Note the use of the Like operator establishing a condition test.

Include in that construction the Not operator to exclude the possibility. Thus str$ Not (Like "*N*N*") excludes any str$ that does not include at least two Ns. (Take care over the brackets.)

Note also, the use of the * (asterisk) and ? (question mark) wild cards. These can be included in any order and any combination in the construction of your condition test.

* stands for any sequence of characters, of any length, including zero length.

? stands for any single character. It is used in to locate a character at a certain position within a string.

The Importance of Case Order

Remember a Case condition takes priority over those lower in the Select Case statement. That explains our function's treatment of the strings in Rows 2 and 3 of the spreadsheet, both of which end in H:

Letter Case — Capitals and Lower Case

By default, VBA is sensitive to the letter case of the text it is processing. That's to say, it regards bn as a different string entirely to BN. That's why our function is not treating the strings the same in Rows 26 and 27 of the spreadsheet.

To prevent VBA's sensitivity, place at the top of the module that contains your Select Case statement the line of code Option Compare Text.

Your Support for DMW TIPS

Please support this website by making a donation to help keep it free of advertising and to help towards cost of time spent adding new content.

To make a contribution by PayPal in GBP (£ sterling) —

To make a contribution by PayPal in USD ($ US) —