DMW logo
(t) +44 (0)1732 833085
(e) david@consultdmw.com

How to Use Select Case Statement in VBA

How to use Select Case statements in VBA procedures to create custom functions and to control program flow.

Last updated on 2021-01-04 by David Wallis.


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.

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:

Select Case test-expression

Case condition1

code to execute for condition1;

Case condition2

code to execute for condition2


Case conditionN

code to execute for conditionN

Case Else

code to execute any outstanding condition

End Select

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.


Demonstration of Select Case Statement

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

Function fnSeason(dte As Date) As String

Dim season$

Select Case Month(dte)

Case 1, 2, 12

season$ = "Winter"

Case 3, 4, 5

season$ = "Spring"

Case 6 To 8

season$ = "Summer"

Case 9 To 11

season$ = "Autumn"

Case Else

season$ = "Unknown"

End Select

fnSeason = season$

End Function

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:

Function fnSeason(dte As Date) As String

Dim season$

Select Case Month(dte)

Case 1, 2, 12 : season$ = "Winter"

Case 3, 4, 5 : season$ = "Spring"

Case 6 To 8 : season$ = "Summer"

Case 9 To 11 : season$ = "Autumn"

Case Else : season$ = "Unknown"

End Select

fnSeason = season$

End Function

Excel seasons formula

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.

Delete record message box

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.

Sub cmdDeleteRecord_OnClick()

Dim msg$, icon&, title$

msg$ = "Archive record before deleting it?"

icon& = vbYesNoCancel + vbQuestion

title$ = "DELETE RECORD"

Select Case MsgBox(msg$, icon&, title$)

Case vbYes

Call subArchiveRecord

Case vbNo

Call subDeleteRecord

Case Else

msg$ = "No action has been taken"

icon& = vbOKOnly + vbExclamation

MsgBox msg$, icon&, title$End Select

End Select

End Sub

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:

Function fnSeason(dte As Date) As String

Dim season$

Select Case Month(dte)

Case Is <= 2, 12 : season$ = "Winter"

Case Is <= 5 : season$ = "Spring"

Case Is <= 8 : season$ = "Summer"

Case Is <= 11 : season$ = "Autumn"

End Select

fnSeason = season$

End Function


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:

String test function

This is the function returning the values in Column B:

Function fnStringTest(str$) As String

Dim bln As Boolean

Select Case True

Case str$ Like "B*" Or str$ Like "A*"

str$ = "Begins with A or B"

Case str$ Like "*H"

str$ = "Ends in H"

Case str$ Like "*A*"

str$ = "Contains an A"

Case str$ Like "*N*N*"

str$ = "Contains at least two Ns"

Case str$ Like "?M*"

str$ = "Second character is M"

Case str$ Like "*G?"

str$ = "Second last character is G"

Case InStrB(1, str$, " ", vbTextCompare) = 0

str$ = "No space"

Case Else

str$ = vbNullString

End Select

fnStringTest = str$

End Function

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:

String test function

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) —

If you’d like an invoice to account for your donation, let me know how much you’re donating —

Invoice

Thanks, in anticipation.

SSL Cerification

“A great lathe operator commands several times the wage of an average lathe operator, but a great writer of software code is worth 10,000 times the price of an average software writer.”

Bill Gates