Today’s focus is on the VBA Select Case statement, which is often comfortable to use when you need to execute different code depending on the value of a certain expression. Usually you won’t be using Select Case when you need to differentiate between less than 3 specific values of an expression. In this post I will want to cover both the How of using Select Case as well as the When and Why.
VBA Select Case
Let’s start with the definition and a few simple examples of how to use the VBA Select Case statement.
Select Case definition
Below is the definition of the Select Case statement:
Select Case testexpression [ Case expressionlist [ statements ] ] ... [ Case Else [ elsestatements ] ] End Select
The Select Case block basically contains a list of Cases (“expressionlist” expressions) followed by a list of statements to be executed whenever a specific “expressionlist” is evaluated to be true or matches the “testexpression”. Only the statements for the first matched Case are executed. The remaining ones will be omitted. Notice also the Case Else statement. Case Else defines a list of “elsestatements” to be executed if none of the remaining Cases are matched/evaluated true. It is a failover Case scenario if you will or simply a Case covering the whole remaining spectrum of Cases. Enough of this – time for some examples.
Select Case examples
Dim direction as String '... Select Case direction Case "Forward" Debug.Print "Move forward!" Case "Backward" Debug.Print "Move back!" Case "Left" Debug.Print "Turn left!" Case "Right" Debug.Print "Turn right!" End Select
The above is a good example of how to apply the Select Case statement. Select Case is usually used when we have a discreet spectrum of values for which there are separate statements we wish to execute for each. Select Case works also for numbers:
On Error Resume Next '... Select Case Err.Number Case 11: Debug.Print "Divide by zero!" Err.Clear Case 6: Debug.Print "Overflow!" Err.Clear Case Else: Debug.Print "Other error" Err.Clear End Select
Err for those unacquainted with VBA Error Handling is an object managing raised errors. When an error is raised the Err.Number variable is populated with the VBA number of the encountered error. In case we want to define separate handlers for each error number the VBA Select Case statement will definitely be the appropriate tool.
Select Case Is
With Select Case we are used to each Case being specified for a specific single value of our “testexpression”. However, VBA facilitates also the Is statement which can be used in tandem with the Case expression to compare our value using operators such as greater than (>), less than (<), greater or equal to (>=) and so on.
Dim ageUS as Long '...in most states... Select Case ageUS Case Is < 17 Debug.Print "You can't drive or drink alcohol" Case Is < 21 Debug.Print "You can drive, but can't drink alcohol" Case Else Debug.Print "Now you can get drunk or drive. But not at the same time!" End Select
The Is statement again will evaluate from the top and execute only statements for the first true Case expression. Hence, if you are 16 you won’t get the remaining 2 messages.
Select Case with ranges
Similarly Select Case allows you to specify a range of values using the To statement:
Dim ageUS as Long '...in most states... Select Case ageUS Case 0 to 16 Debug.Print "You can't drive or drink alcohol" Case 17 to 20 Debug.Print "You can drive, but can't drink alcohol" Case Else Debug.Print "Now you can get drunk or drive. But not at the same time!" End Select
Select Case with enumeration
In some cases you might want to enumerate specific values that should be handled by common statements. In such cases be sure to separate them using a comma “,”.
Dim name as String '... Select Case name Case "Michael", "Mike", "Mikey" Debug.Print "Michael" Case "Thomas", "Tom", "Tommy" Debug.Print "Thomas" Case "Andrew", "Andy": Debug.Print "Andrew" Case Else Debug.Print "Basic name unknown" End Select
Select Case vs If-Else vs Iif
Now that we know our way around the VBA Select Case statment let’s remind the other condition statements in VBA we have available and when to use each.
When to use Select Case?
As you probably already learned Select Case is to be used only when you need to evaluate a single expression (in Select Case) which will provide a single value. That value can be handled separately for different Cases. Cases can handles single values, enumerations and numerical comparisons (>,<,>=,= etc.), but they can’t evaluate separate expressions! This is when we should you the If-Else conditions or the Iif function.
When to use If-ElseIf-Else
Use If-ElseIf-Else statements when:
- You need to evaluate a single option that does not simply return a value
- Each option needs to be evaluated as a separate expression
Don’t use If-ElseIf-Else when:
- You need to define a single variable based on a certain condition (use Iif for that – next section)
- You need to evaluate a single expression and execute different blocks of code depending on the result (use Select Case for that)
When to use the Iif function?
If you haven’t heard of the Iif function do read this post. As it is a great simple function that can’t help shorten your VBA code and make it more clear. Use the Iif function when you need to evaluate a single boolean expression to return either of 2 values. Simple example below:
Dim i as Integer, str as String '... str = Iif(i = 10, "=10", "<>10")