VBA Select Case

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.

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.

The VBA Select Case “testexpression” needs to evaluate to one of the supported data types: Boolean, Byte, Char, Date, Double, Decimal, Integer, Long, Object, Short, Single, String

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

Related posts:

Analyst Cave

VBA Select Case – all you need to know