Home > Excel VBA macros > Select Case

Using Select Case in Excel VBA macros

A common way of testing conditions in VBA is If-Then constructions. However if you have several conditions to test, it is often better and less confusing to use Select Case.

Select Case has a simple list-like syntax. For instance:

Sub TestSelectCase()

Select Case Range("A1").Value
    Case Is >= 1000
        Range("B1").Value = "Too expensive"
    Case Is >= 2000
        Range("B1").Value = "Call the police!"
    Case Else
        Range("B1").Value = "Acceptable"
End Select

End Sub

That was easy: You write Select Case followed by the conditions you want to test, and for each condition you make a "Case" with appropriate action. The last "Case Else" works exactly like "Else" in an If-Then test.

Select Case is very flexible. For instance you can write:

Sub TestSelectCase()

Select Case Range("A1").Value
    Case 100, 150, 155 To 200, 220 To 300, 350, "offer"
        Range("B1").Value = 200
    Case 301 To 349
        Range("B1").Value = 275
    Case Else
        Range("B1").Value = 0
End Select

End Sub

Cell B2 gets the value 200, if A1 has the value 100, 150, 350, "offer", 155 to 200 or 220 to 300. If A1 is 301 to 349, B1 will be 275, and all other values in A1 will put a zero in B1.

Select Case also knows the alphabet

You can also use Select Case for alphabetic tests. For instance:

Select Case Range("A1").Value
    Case "a" To "f"
        Range("B1").Value = "A to F"
    Case "g" To "n"
        Range("B1").Value = "G to N"
    Case Else
        Range("B1").Value = "Not between A and N"
End Select

If for instance cell A1 says "hero," cell B1 will get the value "G to N", and if it says "zebra," B1 will get the value "Not between A and N".

However here you must beware that VBA per default distinguishes between upper and lower case. So if you write "Hero" in cell A1 instead of "hero," B1 will get the value "Not between A and N".

This can be changed by writing "Option Compare Text" at the top of the VBA module - then no procedure or function in the module will distinguish between upper and lower case. The counterpart to this is "Option Compare Binary," which will force a binary comparison.

Nesting

Like with If-Then you can nest Select Case or If Then constructions. For instance:

Sub TestSelectCase()

Select Case Range("A1").Value
    Case 1
        'Here we nest a new Select Case
        Select Case Range("B1")
            Case 2
                Range("C1").Value = 3
            Case Else
                MsgBox "Celle B1 has another value than 2"
        End Select
    Case 2
        'And here we nest an If-Then test
        If Range("B1").Value = 2 Then
            Range("C1").Value = 4
        End If
    Case Else
        'Action
End Select

End Sub

Speed

If you need to test a lot of conditions and have a long list of cases, you can gain speed by putting the most likely cases near the top (if you know what is most likely).

Related:

• If-Then
• Excel VBA sitemap