The Select Case Statement | VBA Selection Statements

Introducing the Select Case Statement

Humans often decide on a course of action by evaluating one or more criteria. Likewise, computer programs select a flow of execution (a.k.a. control) by evaluating expressions. The Select Case statement is one of two VBA selection statements (the If-Then-[ElseIf]-[Else] statement being the other).

An execution path is only chosen if the expression evaluates to a specific value. In this sense, the Select Case statement is also called a conditional statement.

The Select Case statement is especially useful when an expression returns different values, each of which leads to a different program execution path.

The header image above shows the Select Case statement’s syntax. Also, the flowchart below illustrates its logic flow.

Salient Points on Usage

There are several important things to note about the Select Case statement:

• The compiler treats a Null control expression return as False.
• The Select Case statement only has the block form shown in the header image above. In contrast, the If–Then-[ElseIf]-[Else] statement has both a single-line and a block form.
• You can include Line labels or line numbers in the Select-block. But the Select Case statement must be the first entry on the block’s first line.
• Control expressions can only be either numeric or string expressions. Unlike the If–Then-[ElseIf]-[Else] statement, the Select Case statement does not work with TypeOf–Is expressions.
• You can nest Select Case statements within preceding Case statements. But each nesting must have its own End Select statement. Also, you can use as many Case statements as needed, but none can appear after the Case Else statement – unless as part of a new nested Select Case statement.
• Although the Case Else statement is optional, you should always include it. That way you control what happens should no control expression return value match occur in all preceding Case statements. Otherwise, program execution (or flow of control) shifts to the line below End Select.

The Select Case Statement in Action

The image below illustrates the terms used to describe a golfer’s performance at a 5-par hole. Each hole at a golf course (a total of 9 or 18, typically) is a 3, 4, or 5-par hole. Par being the designated strokes or shots that a skilled golfer would take to complete a hole. So, the fewer the strokes, the higher the golfer’s score at a hole.

The sample code below demonstrates the Select Case statement’s usage. It decides the golfing term that matches the number of strokes it takes a golfer to put the ball into a 5-par hole.

In brief, the program:

• Declares Byte and String variables byt_Strokes and str_Score (code window line #8). These variables store a golfer’s strokes-to-put at a 5-par hole and the matching scoring term;
• Gets a golfer’s strokes-to-put from the user via the CByte and InputBox functions (line #11). Note that this procedure does not validate the user’s input, but it should as that is the best practice;
• Assigns a scoring term to the golfer’s play by testing the golfer’s strokes-to-put against the score-per-strokes. It uses the Select Case statement (lines #14-33);
• Displays the assigned score to the user using the MsgBox function (line #36).

The CByte function is a built-in type-conversion function that converts expressions into Byte data type. However, CByte throws an error if the user enters non-numeric input, so the Val function would be a better choice here.