EXCEL VIDEO TUTORIALS / EXCEL DASHBOARD REPORTS

Excel Training VBA Lesson 13

Select Case Statement In Excel VBA

<< PREVIOUS LESSON |NEXT LESSON>> | BACK TO EXCEL VBA LEVEL1 TRAINING INDEX

Select Case

The other method of checking for single or multiple conditions is the Select Case Function. This is another method used for decision making based on a condition or criteria. It, in my opinion, is much better than If etc. This has the syntax

Select Case <Expression to test>
    Case <Test1>
        Do something
    Case <Test2>
        Do something
    Case Else
        Do something else
End Select

As you can see the "Select Case" Function is very similar to the "If" Function in that it will only perform some action if a condition is met. While this may seem no better than the "If" Function I feel that it is a MUCH better choice than the "If" Function If more than one condition or expression needs to be tested. Not only is it more efficient but it has a much better structure than the "If" Function. This means it is far easier to read or decipher and believe me you WILL need to go back through your written code frequently to find out a problem (De-bug). While these two reasons alone are enough for me, there is another and that is it has FAR more flexibility. We will first look at the "Select Case" Function in it's simplest form

Sub TheSelectCase()

Select Case Range("A1").Value

Case 100

Range("B1") = 50

End Select

End Sub

As you may have noticed, this does not include a "Case Else" Statement. This is because, like the "Else" Statement in the "If" Function it is optional. The "Select Case" in it's simplest form as shown above, holds no advantage over the "If" Function, in fact it could be argued that it is an incorrect use of the "Select Case" Function. Let us say you need to perform any one of 5 actions depending on the Value of Range A1. If so we could use:

Sub TheSelectCase()

Select Case Range("A1").Value

Case 100

Range("B1").Value = 50

Case 150

Range("B1").Value = 40

Case 200

Range("B1").Value = 30

Case 350

Range("B1").Value = 20

Case 400

Range("B1").Value = 10

End Select

End Sub

This, in my opinion, is a far better structure and easier to read than an "If" Function with multiple "ElseIf" Statements. If none of the above Conditions were met nothing would occur, unless we use the optional "Case Else" Statement, like:

Sub TheSelectCase()

Select Case Range("A1").Value

Case 100

Range("B1").Value = 50

Case 150

Range("B1").Value = 40

Case 200

Range("B1").Value = 30

Case 350

Range("B1").Value = 20

Case 400

Range("B1").Value = 10

Case Else

Range("B1").Value = 0

End Select

End Sub

So If the Value of Range A1 is NOT 100,150,200,350 or 400 then place a Value of 0 (zero) in Range B1. Now while this demonstrates how we can check multiple conditions with the "Select Case" Function, what if we want to perform some action If the Range A1 is equal to any one of the Values 100,150,200,350 or 400. If this is the case (no pun indented) we could use:

Sub TheSelectCase()

Select Case Range("A1").Value

Case 100, 150, 200, 350, 400

Range("B1").Value = Range("A1").Value

Case Else

Range("B1").Value = 0

End Select

End Sub

I don't believe anybody could argue against this being a far better structure than an "If" Function with multiple "Or" Operators.

We used the "If" Function combined with the "And" operator above to demonstrate how to let Excel know if the Value of Range A1 is between two numbers. We can do this also with the "Select Case" Function with even greater ease:

Sub TheSelectCase()

Select Case Range("A1").Value

Case 100 To 500

Range("B1").Value = Range("A1").Value

Case Else

Range("B1").Value = 0

End Select

End Sub

As the above example demonstrates, we use the Keyword "To" to test whether Range A1 is between 100 and 500 and if so place the value of Range A1 in Range B1. We can take this a step further if needed to test for multiple "To" conditions, like:

Sub TheSelectCase()

Select Case Range("A1").Value

Case 100 To 500, 600 To 1100, 1200 To 2000

Range("B1").Value = Range("A1").Value

Case Else

Range("B1").Value = 0

End Select

End Sub

So now with this one "Select Case" Function we can check to see if Range A1 is between any one of three different Values. We could if we needed add more. Another major advantage of the "Select Case" Function over the If Function is that we can use it to determine if certain Text is between two other Text Strings ie; alphabetically. Let's assume you are only interested in the content of Range A1 if the Text within it is alphabetically between "Aardvark" and "Elephant", to do this we could use:

Sub TheSelectCase()

Select Case Range("A1").Text

Case "Aardvark" To "Elephant"

Range("B1").Value = "it's between"

Case Else

Range("B1").Value = "it's not between"

End Select

End Sub

So if the text in Range A1 is "Budgie" range B1 will read "It's between". If the Text in A1 is "Zebra" then Range B1 would read "It's not between".

So as you can see there are many ways within VBA for Excel we can use to evaluate and determine a Value or Text. You will find yourself using the "If" and "Select Case" Functions quite frequently and as I have already indicated, the Select Case is often a much better option.