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.
Select Case < The Expression > Case SomeCondition ‘ Do something Case Else ‘ Do the default action End Select
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
Here are some other Case statements that illustrate the power of Select Case.
Will be true if the expression resolves to a number between 1 to 7 inclusive, 19, or any number greater than 25
Same as Case "Blah"
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.
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.
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!