Categorized | excel macro and vba

VBA Select Case – Using VBA Select Case Statement in Excel




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


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=""> <s> <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

  7. Andrewmix wrote:


    objets publicitaires personnalisés originaux

    Promenoch les spécialistes des objets publicitaires basé à Paris vous propose un large choix de cadeaux d’affaire et d’objets publicitaires : cadeaux entreprises, coffret champagne personnalisé, panier gourmand personnalisable, chocolat publicitaires, coffrets cadeaux beauté publicitaires, parure stylos personnalisable. Retrouvez touts nos objets publicitaires personnalisés ( textiles publicitaires, sac personnalisable, montres personnalisés, stylos publicitaires ) cadeaux d’affaires luxe personnalisable ( maroquineries personnalisés, parures stylos publicitaires,…) on peut également réaliser une large gamme de sacs et bagageries personnalisable ( sacs de voyage publicitaires, sac de sport personnalisé ou des sacs shopping personnalisable), trouvez votre cadeau d’affaire ou votre objet publicitaire sur toutes nos catégories que nous proposons comme les parapluies personnalisable, montres personnalisés, textiles publicitaires, clés USB publicitaires, mug en verre personnalisables, gourde et bouteilles personnalisés, sac kraft personnalisable, sac papier écho-responsable publicitaires. Nous vous proposons également des objets publicitaires écologique qui respecte la nature ( plantes personnalisable, Mug en bois personnalisé, verre en bambou publicitaire, sac shopping en coton en toile ou en fibre de bambou écologique, carnets publicitaires et aussi des stylos écho-responsable personnalisables).

    Vous recherchez une idée cadeau pour la fin d’année ? Promenoch vous propose une large gamme des idées cadeaux 2018/2019 personnalisable (montres personnalisables, plantes publicitaires, paniers gourmand personnalisable, cadeau d’entreprise publicitaires, articles et décorations noël publicitaires). Vous pouvez personnaliser vos objets publicitaires avec vos photos ou bien votre logo afin de diffuser au plus grand nombre votre marque ou image.

    Nos cadeaux d’affaires et objets publicitaires sont le support de communication parfait pour les consultants les CE , mairies , administrations, entreprises , commerces, garages, concessionnaires , ambulanciers , cliniques, secteur de la santé , laboratoires, BTP, société de transport, agence de consulting, cabinet de conseils , cabinet d’avocats.

    Nous vous proposons également nos articles de sport publicitaires ( ballon personnalisable, écharpe supporteurs publicitaires, trophées personnalisés, sac de sport publicitaires, poignet personnalisable, gourde personnalisé, jeux de pétanque personnalisable, accessoires de fitness publicitaires, tapis de yoga publicitaires, dossards personnalisés, Banderoles publicitaires personnalisables en non tissé 130gr
    En rouleau ou découpées).

    Promenoch sont les spécialistes des objets publicitaires et cadeaux d’affaires propose un large choix de calendriers publicitaires ( calendriers bancaires personnalisés 7 mois ou 13 mois, calendriers chevalet personnalisables) ou encore agendas publicitaires en simili cuir, balacron, vivella, sense cuir, charme. Nous avons aussi à vous proposer les grands classiques indémodables et très efficaces en terme de publicité par l’objet que sont les clés USB personnalisées et les stylos publicitaires, verres en plastique personnalisable, plaid polaire personnalisés, Mug en pailles personnalisable, sac isotherme publicitaires, sacs shopping publicitaires, gourde en tritan ou en plastique publicitaires, Éventails en bois ou en plastique personnalisable, conférenciers personnalisés, sac pliable shopping personnalisable, couvre casque ski publicitaires, badges personnalisable, mug avec cuillère personnalisable, verre en bambou personnalisable, gobelet en carton ou en plastique publicitaire, chapeaux publicitaire, agendas publicitaires, calendriers personnalisés, diffuseur d’huiles essentiels personnalisable, t-shirts publicitaires, chemises personnalisées, vêtements de hautes visibilité personnalisable ( gilet de sécurité personnalisé, pantalon de sécurité publicitaire).

    Pour vos salons, congrès ou évènement, retrouvez un large choix de drapeaux publicitaires, de tonnelle personnalisables ou de portes document personnalisés.
    Les lanyards personnalisés et les beach flag publicitaires sont parfaits pour les événements sportifs ou bien les festivals.

    Découvrez la gamme d’objets publicitaires de luxe de Promenoch, pour les entreprises exerçant dans le secteur du luxe/haut de gamme à offrir à vos collaborateurs pour la promotion de vos entreprises. Promenoch dispose d’une gamme variée d’objets publicitaires de luxe, de parfaits support de communication pour avoir de la visibilité et atteindre la cible adéquate.

    Pour les restaurants et hôtels de luxe Promenoch vous propose des objets publicitaires de luxe destinés à votre clientèle. Promenoch dispose d’une gamme de produits de luxe publicitaires. Pour les restaurants et hôtels 5 étoiles nous avons sélectionné des portes menus personnalisables, des cartes restaurants publicitaires, des set de tables personnalisés, des paniers à pains, des porte-billets personnalisables.

    Nous avons aussi des produits de luxe tels que les sacs personnalisés, des portefeuilles personnalisable, des parure de stylo de luxe publicitaire, des agendas et blocs note publicitaires. Touts nos objets de luxes publicitaires sont personnalisables selon vos envies. Vous pouvez y marquer le logo, le nom, le slogan de votre entreprise, etc. Offrez les objets de luxe à l’image de votre entreprise à vos meilleurs collaborateurs ( Clients, partenaires d’affaires, etc.).

    Retrouvez notre catégorie des chaises transats publicitaires, parasols personnalisables, serviettes et paréos personnalisés, sacs de plage publicitaires . Ces objets publicitaires sont parfaits pour les clubs de vacances, les plages privées ou les campings. Personnaliser ces objets de plage en y inscrivant le nom de votre hôtel ou de votre spa, afin de donner à votre enseigne une bonne visibilité.

    Ces produits publicitaires sont proposé par Promenoch, le spécialiste des objets publicitaires et cadeaux d’affaires basé à Paris. Promenoch vous garantie une distribution de ses produits personnalisables partout en France et dans le monde entier (Belgique, Suisse, Luxembourg, Afrique. etc.). Notre équipe logistique vous garantie une livraison express de vos commandes quelle que soit la destination

  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 […]

  2. Modification to my VBA code wrote:

Subscribe

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

Translate

English flagItalian flagKorean flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagRussian flagDutch flagHindi flagFilipino flagIndonesian flagThai flagTurkish flagPersian flag
treeemap software for excel


ARCHIVES