The Case for Select Case

Martin LissKeep everyone healthy; Get Vaccinated
CERTIFIED EXPERT
Almost 50 years of programming experience. Click '+ More' in my "Full Biography" to see links to some articles I've written.
Published:
Updated:

As a person who answers a lot of questions, I often see code that could be simplified, made easier to read, and perhaps most importantly made easier to maintain if the code was modified to use the Select Case statement. This article explains how to do that.


An example

Let’s say I wanted to determine if a variable (in this case a String) contains certain values, so I might write something like this.

If MyVar = "This" Or MyVar = "is" Or MyVar = "a" Or MyVar = "test" Then
    MsgBox "I'm interested in those words"
End If

At one time in my programming career when I would write a line like the first one, I’d say to myself “I wish that I didn’t need to repeat the “MyVar =” and could write the following instead.

If MyVar = "This" Or "is" Or "a" Or "test" Then

Experience, however, taught me that I’d get a syntax error. Fortunately I eventually discovered the Select Case statement and I was able to write this instead which is similar.

Select Case MyVar
    Case "This", "is", "a", "test"
        MsgBox "I'm interested in those words"
End Select

The rest of this article explains how to use the powerful Select Case statement.


The basic Select Case syntax

Select Case < The Expression >
    Case SomeCondition
        ‘ Do something
    Case Else
        ‘ Do the default action
End Select

Notes:

  • For the purposes of this article, “The Expression” is any valid expression that resolves to a string, a numeric value, or a Boolean value. In the case of Excel that includes cell values.  
  • When Visual Basic encounters a Select Case statement, it evaluates the Cases top to bottom, and once it finds that the Case is True it executes the code in that Case and ignores the rest.
  • There can be multiple Case statements
  • The Else case is optional


An expanded version of our Select Case above might look like this.

Select Case MyVar
    Case "This", "is"
        MsgBox "It’s one of the first two words"
    Case "a", "test"
        MsgBox "It’s one of the last two words" 
    Case Else
        MsgBox “Word not found”
End Select


In Case you want to know more

Here are some other Case statements that illustrate the power of Select Case.

Case 1 to 7, 19, > 25

Will be true if the expression resolves to a number between 1 to 7 inclusive, 19, or any number greater than 25

Case Is = "blah"

Same as Case "Blah"

Case "alpha" To "omega"

Visual Basic can spell and any value that is alphabetically between the two (like “great”)  will be true. Be warned however that “Great” is not the same as “great” and the former will not be true. One way to avoid that is to put Option Compare Text at the top of your code module. When you do that all text comparisons will non-case sensitive. Another way is this which compares the upper case versions of the text.

Select Case UCase(Range("B2"))
    Case "ALPHA" To "OMEGA"

You can also nest Select Case statements like this example.

Select Case Range("B2")
    Case "alpha" To "omega"
        Select Case Range("B2")
            Case "coding", "is", "fun"
                MsgBox "I agree"
        End Select
End Select

The MsgBox will be displayed if it contains one of those three words.


And what is perhaps my favorite

Remember where I said that “The Expression” could be anything that resolves to a string, a numeric value, or a Boolean value? Well, True (or False) is a Boolean value and so you can do this:

Select Case True
    Case Range("B1") = 5
        ' Do something
    Case Range("C4") = "Done"
        ' Do something else
    Case Else
        ' Do some default action
End Select

And the first one of those things that is true will be executed. I find many uses for that.


Finally

For more suggestion about how to write understandable and maintainable Visual Basic code see my article on the subject.


If you find that this article has been helpful, please click the “thumb’s up” button below. Doing so lets me know what is valuable for EE members and provides direction for future articles. It also provides me with positive feedback in the form of a few points. 


If you have any suggestions for improvement or if you encounter any bugs, please send me a message. Thanks!


4
2,404 Views
Martin LissKeep everyone healthy; Get Vaccinated
CERTIFIED EXPERT
Almost 50 years of programming experience. Click '+ More' in my "Full Biography" to see links to some articles I've written.

Comments (0)

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.