Categorized | excel macro and vba

VBA Select Case – Using VBA Select Case Statement in Excel




TumblrStumbleUponRedditLinkedInAIMDiggEvernoteWhatsAppMySpaceKindle It

The VBA Select Case Statement is an important construct in the VBA language. The Select Case statement in VBA allows the program to execute one set of statements out of many based on what a given expressions evaluates to. In today’s post we will look at some basic and intermediate ways in which it can be used when writing VBA code.

Basic Form of the VBA Select Case Statement

1
2
3
4
5
6
7
Select Case expression_to_test
Case expression_value_1 : Code to Execute When expression_to_test = expression_value_1
Case expression_value_2 : Code to Execute When expression_to_test = expression_value_2
Case expression_value_3 : Code to Execute When expression_to_test = expression_value_3
'Other Case Expressions Here    .....
Case Else: MsgBox "Case Else : The Code to Execute When no other expression = TRUE
End Select

Here are the various parts of the Select Case Statement in VBA:

Expression To Test: The test expression is the condition based on which we would like to execute one of the many available code segments.

Various Case Statements: The various expressions to which the above test expression is matched. Each of the expressions may have a set of VBA code statements associated with them so that whenever that particular case evaluates to matches the test expression, the code gets executed.

Case Else: If none of the cases specified match the specified expression, the code mentioned in case else statement would get executed. Remember that the case else statement is optional. However when mentioned, it would the one that get executed if no other match is found.

1
2
3
4
5
6
Sub My_Select_Case_1()
Select Case "Apple" = "Orange"
Case True: MsgBox "The expression is TRUE"
Case False: MsgBox "The expressions is FALSE"
End Select
End Sub

The basic form of VBA procedure with a select case statement is shown above. The various case statements simply check whether the expression given in the Select Case (First line), meets any of the expression values specified against the various case statements. In this case the expression_to_test is whether “Apple” = “Orange”. If the expression evaluates to TRUE, the first case option (Case True) would get executed. If not, the second statement would get executed.

VBA Select Case Statement to Check Condition For Numbers

Here is a working example of the select case statement. If you copy and paste it in a worksheet (Use Alt + F11) to open the VBA IDE and enter this on any sheet or module.

1
2
3
4
5
6
7
8
9
10
11
Sub My_Select_Case_2()
Dim my_val As Long

my_val = 2
Select Case my_val
Case 1: MsgBox "The number is one"
Case 2: MsgBox "The number is two"
Case 3: MsgBox "The number is three"
Case Else: MsgBox "Case Else : The number is not one, two or three"
End Select
End Sub

Bear in mind that in the event that multiple select case expressions evaluate to TRUE, the very first expression that evaluates to TRUE will be used and the corresponding code will get executed. You can also use comparison operators such as “>=”, “> ” , “<”, “<=” and “=” in the various case statements. The IS keyword is used with such operators. So if you want to compare whether a given expression evaluates to less than or greater than a number, you can write:

1
2
3
4
5
6
7
8
9
10
Sub My_Select_Case_3()
Dim my_val As Long

my_val = 1
Select Case my_val
Case Is < 1: MsgBox "The number is less than one"
Case Is = 1: MsgBox "The number is equal to one"
Case Is > 1: MsgBox "The number is greater than one"
End Select
End Sub

VBA Select Case Statement For Text / String- Part I

The select statement works for text as well as numbers. So you can match the given string expression to a set of values and have the respective code execute accordingly. The various strings have to be enclosed in double-quotes (“”). In the example given below, we test whether a given string (my_val) is an “Apple”, “Orange” or a “Pineapple”. If a match is found, the respective code mentioned against that case will be executed. If no match is found in this particular case, the none of the code segments will get executed.

1
2
3
4
5
6
7
8
9
10
Sub My_Select_Case_3_Text()
Dim my_val As String

my_val = "Pineapple"
Select Case my_val
Case "Apple": MsgBox "The fruit is Apple"
Case "Orange": MsgBox "The fruit is Orange"
Case "Pineapple": MsgBox "The fruit is Pineapple"
End Select
End Sub

More about using text conditions in a select case statement later.

Group Options in the VBA Select Case Statement

Rather than having each option specific as a separate case, you can have a number of options clubbed in as a single case. If any one of the grouped options evaluates to true, the code mentioned within that case would get executed. For example, in the case given below, the statement mentioned against “Case 11, 12, 13, 14, 15″ would get executed since the given expression matches this case.

1
2
3
4
5
6
7
8
9
10
11
12
Sub My_Select_Case_4()
Dim my_val As Long

my_val = 14
Select Case my_val
Case Is < 1: MsgBox "The number is less than 1"
Case 1, 2, 3, 4, 5 : MsgBox "The number is between 1 and 5"
Case 6, 7, 8, 9, 10 : MsgBox "The number is between 6 and 10"
Case 11, 12, 13, 14, 15 : MsgBox "The number is between 11 and 15"
Case Else: MsgBox "The number is greater than 15"
End Select
End Sub

Specify a Continuous Range of Options in a VBA Select Case Statement

When one needs to group the various options in a VBA Select Case, they need not be mentioned individually. To group all the values falling within a given span, we can simply write the select case by specifying the minimum and maximum values using the “To” keyword. Just bear in mind that the Select Case will work for both a range of numbers and alphabets.

1
2
3
4
5
6
7
8
9
10
11
Sub My_Select_Case_5()
Dim my_val As Long

my_val = 20
Select Case my_val
Case 1 To 9: MsgBox "The number is between 1 and 9"
Case 10 To 19: MsgBox "The number is between 10 and 19"
Case 20 To 29: MsgBox "The number is between 20 and 29"
Case Else: MsgBox "The number is less than 1 or greater than 29"
End Select
End Sub

Specifying Multiple Conditions within a Single Select Case Statement in VBA

Multiple conditions can be specified within a single select case clause. In such cases each of the individual conditions mentioned as a case needs to be separated from the preceding one with a comma (,). VBA treats the commas the same as the OR operator so if any of the given set of conditions in a single case evaluate to TRUE, the VBA code associated with the given ‘case’ will get executed.

1
2
3
4
5
6
7
8
9
10
11
Sub My_Select_Case_6()
Dim my_val As Long

my_val = 15
Select Case my_val
Case 1 To 4, 5 To 9: MsgBox "The number is between 1 and 9"
Case 10 To 14, 15 To 19 : MsgBox "The number is between 10 and 19"
Case 20 To 24, 25 To 29 : MsgBox "The number is between 20 and 29"
Case Else: MsgBox "The number is less than 1 or greater than 29"
End Select
End Sub

Infact a single case statement can have both text and numeric expressions. In the example given below, we combine numeric and text expressions in a single case statement. In the earlier select case, the first expressions matches the test condition while in the later, the second one meets the requirement.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Sub My_Select_Case_7()
Dim my_val As Variant

my_val = "Mike"
Select Case my_val
Case 1 To 4, "Mike": MsgBox "The value is between 1 and 4 or is Mike"
Case 10 To 14, "Julie" : MsgBox "The value is between 10 and 14 or is Julie"
End Select

my_val = 12
Select Case my_val
Case 1 To 4, "Mike": MsgBox "The value is between 1 and 4 or is Mike"
Case 10 To 14, "Julie" : MsgBox "The value is between 10 and 14 or is Julie"
End Select

End Sub

VBA Select Case Statement For Text / String- Part II

In the previous section dealing with text conditions in a select case statement, we said that we will look at some additional details later. So here they are:

Now just like numbers, text values can also be grouped. We can even use operators such as “>=”, “> ” , “<”, “<=” and “=”. However bear in mind that, by default, if no other options are specified, a string “Mike” is not quite the same as “mike”. In general, the order of comparison is :

By Default or when Option Compare Binary is specified:
A < B < E < Z < a < b < e < z < À < Ê < Ø < à < ê < ø

If you want “A” to be treated equal to “a” and so on and so forth for all alphabets, insert the following lines in your VBA code outside of any function or procedure:

1
Option Compare Text

When Option Compare Text is specified, the order of comparison is:
(A=a) < ( À=à) < (B=b) < (E=e) < (Ê=ê) < (Z=z) < (Ø=ø)

If we don’t specify any Option Compare Statement, the following VBA code will have the second condition match the specified expression. This is because when noting is specified, the Option Compare Binary is used as default.

1
2
3
4
5
6
Sub My_Select_Case_8()
Select Case "q"
    Case Is < "Z": MsgBox "The text value is less than Z"
    Case Is > "Z": MsgBox "The text value is greater than Z"
End Select
End Sub

Now suppose we want to see if a given string occurs before another (irrespective of upper or lower case), we will need to explicitly specify the Option Compare Text statement in the VBA code. In the example given below, we would like to check if the given name occurs before some other name.

1
2
3
4
5
6
7
Option Compare Text
Sub My_Select_Case_9()
Select Case "robert"
    Case Is < "Mike": MsgBox "Occurs before Mike"
    Case Is > "Mike": MsgBox "Occurs after Mike"
End Select
End Sub

It is also possible to group text expressions using the To keyword, just like we did in the case of numeric expressions. In the example given below, the given value (“p”) will be checked against all the given cases. Since “p” falls between “n” and “z”, therefore the second case statement will get executed.

1
2
3
4
5
6
7
Sub My_Select_Case_10()
Select Case "p"
    Case "a" To "m": MsgBox "Between a and m"
    Case "n" To "z": MsgBox "Between n and z"
    Case Else: MsgBox "Case Else"
End Select
End Sub

You can download a copy of the workbook with various VBA Select Case statements here or click on the button below

VBA Select Case Statements

TumblrStumbleUponRedditLinkedInAIMDiggEvernoteWhatsAppMySpaceKindle It

Excel Formula, Excel Chart, Excel Macro, Excel VBA, Pivot Table Excel, Excel Dashboard

What Do You Think ?


XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>


Comments and Trackbacks

  1. Jessica wrote:

    What if I have multiple expressions to test?

  2. Someone wrote:

    1
    2
    3
    4
    5
    6
    7
    Dim expr() as String, e
    expr = Array(expr1, expr2, expr3, ...)
    For Each e In expr
        Select Case e
        ...
        End Select
    Next

    In this example, the cases must be the same for all expressions. If each expressions has different cases, you need a Select Case for each expression.

    Hope it helps!

  3. Wese wrote:

    “” comparison operator is not mentioned.
    It doesn’t work on a comma delimited list of values:
    Select Case q
    Case Is 404, 1808, 1646

    If q is not equal to 1808, it returns False (should be True)

  4. Wese wrote:

    The “not equal” operator (two facing angle brackets) didn’t print in my question -sorry.

  5. coto wrote:

  6. coto wrote:

    Dim expr() as String, e
    expr = Array(expr1, expr2, expr3, …)
    For Each e In expr
    Select Case e

    End Select
    Next

  1. how does the case comand work wrote:

    [...] Select Case Statement in Excel VBA Macro Code. Alternative to Multiple If, Or, And Else Statements VBA Select Case – Using VBA Select Case Statement in Excel | Excel & VBA – Databison Select…Case Statement (Visual [...]

Subscribe

Keep up with the latest stories - Delivered right to your inbox
feedburner

Translate

English flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagDutch flagHindi flagPolish flagSwedish flagFilipino flagHebrew flagIndonesian flagUkrainian flagThai flagTurkish flag
treeemap software for excel