• Login
  • Courses
  • Books
  • Cheat Sheets
  • Tutorials Archive
  • VBA Code Generator
  • Contact

Power Spreadsheets

Excel and VBA tutorials and training. Learn how to use Microsoft Excel and Visual Basic for Applications now.

Excel VBA Select Case And Operator in 5 Easy Steps (+ Free Easy-To-Adjust Excel Workbook Example)

In this Excel VBA Select Case And Operator Tutorial, you learn how to create an Excel VBA Select Case And operator statement to conditionally execute a set of statements based on:

  • An expression's value; and
  • The And Operator.

The Excel VBA Select Case And operator snippet template/structure you learn in this Tutorial is a specific application of more general Excel VBA Select Case snippet templates/structures. You may (also) be interested in the following related snippet templates/structures:

  • Excel VBA Select Case Between 2 Values.
  • Excel VBA Select Case Multiple Values.
  • Excel VBA Select Case Multiple Conditions.
  • Excel VBA Select Case Multiple Test Expressions.

I link to these Tutorials in the Related Excel Macro and VBA Training Materials and Resources Section.

This Excel VBA Select Case And Operator Tutorial is accompanied by an Excel workbook with the data and VBA code I use. Get this example workbook (for free) by clicking the button below.


Get immediate free access to the Excel VBA Select Case And operator example workbook

The VBA code in the Excel workbook that accompanies this Excel VBA Select Case And Operator Tutorial is (always) stored in the Visual Basic Editor (VBE). If you don't know how to work with the VBE, I suggest you read my Visual Basic Editor (VBE) Tutorial. I link to this Tutorial in the Related Excel Macro and VBA Training Materials and Resources Section.

Table of Contents

  • Related Excel Macro and VBA Training Materials and Resources
  • Excel VBA Select Case And Operator Snippet Template/Structure
  • Step-by-Step Process to Create an Excel VBA Select Case And Operator Statement
  • How (and Why) the VBA Select Case And Operator Statement Works
  • Excel VBA Select Case And Operator Example Macro
  • Download the Excel VBA Select Case And Operator Example Workbook
  • Related Excel Macro and VBA Training Materials and Resources

Related Excel Macro and VBA Training Materials and Resources

The following Excel Macro and VBA Tutorials may help you better understand and implement this VBA Select Case And Operator Tutorial.

  • Tutorials about general macro and VBA constructs and structures:
    • Tutorials for Beginners:
      • Excel Macros: Click here to open.
      • Excel VBA: Click here to open.
    • Enable macros in Excel: Click here to open.
    • Work with the Visual Basic Editor (VBE): Click here to open.
    • Create Sub procedures: Click here to open.
    • Work with:
      • Variables: Click here to open.
      • Data types: Click here to open.
      • Functions: Click here to open.
  • Tutorials with practical VBA applications and macro examples:
    • MsgBox: Click here to open.

This Excel VBA Select Case And Operator Tutorial is part of a more comprehensive series of Excel VBA Select Case Tutorials.

  • Excel VBA Select Case Tutorial: Click here to open.
  • Excel VBA Select Case Or: Click here to open.
  • Excel VBA Select Case And Operator: Click here to open.
  • Excel VBA Select Case Multiple Test Expressions: Click here to open.
  • Excel VBA Select Case Like Wildcard: Click here to open.
  • Excel VBA Select Case Inside For… Next Loop: Click here to open.
  • Excel VBA Select Case Range of Cells: Click here to open.

You can find more Excel and VBA Tutorials in the organized Tutorials Archive: Click here to visit the Archives. The following are some of my most popular Excel Tutorials and Training Resources:

  • Excel Keyboard Shortcuts Cheat Sheet: Click here to open.
  • Work with the Excel XLOOKUP Function: Click here to open.
  • Excel Power Query (Get & Transform) Tutorial for Beginners: Click here to open.

If you want to learn how to automate Excel (and save time) by working with macros and VBA, you may be interested in the following Premium Excel Macro and VBA Training Materials:

  • Premium Courses at the Power Spreadsheets Academy: Click here to open.
  • Books at the Power Spreadsheets Library: Click here to open.
  • VBA Cheat Sheets: Click here to open.

If you want to save time when working with macros and VBA, you may be interested in AutoMacro: Click here to learn more about AutoMacro (affiliate link). AutoMacro is an add-in for VBA that installs directly into the VBE. Depending on the version, AutoMacro comes loaded with:

  • Code generators.
  • An extensive code library.
  • The ability to create your own code library.
  • Advanced coding tools.

If you need help with Excel tasks/projects, you may be interested in working with me: Click here to learn more about working with me.


Get immediate free access to the Excel VBA Select Case And operator example workbook

Excel VBA Select Case And Operator Snippet Template/Structure

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vba-select-case-and/
Select Case True
    Case Case1Expression1 And Case1Expression2 And ... And Case1Expression#
        CaseStatements1
    Case Case2Expression1 And Case2Expression2 And ... And Case2Expression#
        CaseStatements2
    '...
    Case Case#Expression1 And Case#Expression2 And ... And Case#Expression#
        CaseStatements#
    Case Else
        ElseStatements
End Select


Get immediate free access to the Excel VBA Select Case And operator example workbook

Step-by-Step Process to Create an Excel VBA Select Case And Operator Statement

(1) Enter the opening and closing statements of the Select Case statement:

  • Select Case.
  • End Select.
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vba-select-case-and/
Select Case '...
    '...
End Select

(2) Set the test expression VBA uses to identify the set of statements to execute to True.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vba-select-case-and/
Select Case True
    '...
End Select

(3) Specify the case expressions used by VBA to identify the set of statements to execute. Each case expression is preceded by the Case keyword.

To create a VBA Select Case And operator statement, specify case expressions as logical expressions using the And operator.

  • Case Case1Expression1 And Case1Expression2 And … And Case1Expression#.
  • Case Case2Expression1 And Case2Expression2 And … And Case2Expression#.
  • …
  • Case Case#Expression1 And Case#Expression2 And … And Case#Expression#.

The And operator:

  • Carries out a logical conjunction of 2 expressions; and
  • Returns:
    • True if both expressions return True.
    • False if either:
      • Any expression returns False; or
      • Both expressions return False.
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vba-select-case-and/
Select Case True
    Case Case1Expression1 And Case1Expression2 And ... And Case1Expression#
        '...
    Case Case2Expression1 And Case2Expression2 And ... And Case2Expression#
        '...
    '...
    Case Case#Expression1 And Case#Expression2 And ... And Case#Expression#
        '...
    '...
End Select

(4) Specify the set of statements to execute when the applicable logical case expression (you specified in step #3 using the And operator) returns True.

  • CaseStatements1.
  • CaseStatements2.
  • …
  • CaseStatements#.
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vba-select-case-and/
Select Case True
    Case Case1Expression1 And Case1Expression2 And ... And Case1Expression#
        CaseStatements1
    Case Case2Expression1 And Case2Expression2 And ... And Case2Expression#
        CaseStatements2
    '...
    Case Case#Expression1 And Case#Expression2 And ... And Case#Expression#
        CaseStatements#
    '...
End Select

(5) Specify the set of statements to execute if no logical case expression (you specified in step #3 using the And operator) returns True. These catch-all statements:

  • Follow the Case Else keyword; and
  • Are optional.
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vba-select-case-and/
Select Case True
    Case Case1Expression1 And Case1Expression2 And ... And Case1Expression#
        CaseStatements1
    Case Case2Expression1 And Case2Expression2 And ... And Case2Expression#
        CaseStatements2
    '...
    Case Case#Expression1 And Case#Expression2 And ... And Case#Expression#
        CaseStatements#
    Case Else
        ElseStatements
End Select


Get immediate free access to the Excel VBA Select Case And operator example workbook

How (and Why) the VBA Select Case And Operator Statement Works

The Select Case statement does the following:

  1. Compare a test expression to several case expressions.
  2. Determine how to proceed based on the case expression that matches the test expression.

The And operator is a logical operator. Logical operators return a Boolean value (True or False) when evaluated.

If you want to create an Excel VBA Select Case And operator statement:

  • The test expression; and
  • All case expressions;

Must return a Boolean value (True or False) when evaluated. This ensures that (both) the test expression and all case expressions are of the same data type.

Do the following to achieve this:

  • Set the test expression to the Boolean value True.
  • Specify the case expressions as logical expressions using the And operator.

When a case expression returns True, the Select Case statement:

  1. Executes the set of statements associated to the applicable Case clause; and
  2. Exits the Select Case statement.


Get immediate free access to the Excel VBA Select Case And operator example workbook

Excel VBA Select Case And Operator Example Macro

The VBA Select Case And operator example macro (below) displays a message box. The message inside the message box varies, depending on:

  • The current month; and
  • The current day of the month.

I use a VBA Select Case And operator statement to specify the proper message.

Sub SelectCaseAndOperator()
    'Source: https://powerspreadsheets.com/
    'More information: https://powerspreadsheets.com/vba-select-case-and/
    
    'Declare variables to represent:
        'Message to be displayed in message box
        'Current month (as a number between 1 and 12)
        'Current day of the month
    Dim MyMsgBoxMessage As String
    Dim MyCurrentMonth As Integer
    Dim MyCurrentDay As Integer
     
    'Identify current month and day of the month
    MyCurrentMonth = Month(Date:=Date)
    MyCurrentDay = Day(Date:=Date)
     
    'Assign a string to the MyMsgBoxMessage variable, depending on:
        'The current month; and
        'The current day of the month
    Select Case True
        Case (MyCurrentMonth <= 6) And (MyCurrentDay <= 15)
            MyMsgBoxMessage = "It's the first half of a month in the first half of the year"
        Case (MyCurrentMonth <= 6) And (MyCurrentDay >= 16)
            MyMsgBoxMessage = "It's the second half of a month in the first half of the year"
        Case (MyCurrentMonth >= 7) And (MyCurrentDay <= 15)
            MyMsgBoxMessage = "It's the first half of a month in the second half of the year"
        Case (MyCurrentMonth >= 7) And (MyCurrentDay >= 16)
            MyMsgBoxMessage = "It's the second half of a month in the second half of the year"
    End Select
     
    'Display message box with string held by the MyMsgBoxMessage variable
    MsgBox MyMsgBoxMessage

End Sub

The VBA Select Case And operator statement inside the VBA Select Case And operator example macro specifies 4 case expressions as logical expressions using the And operator.

The 2 conditions I test inside each logical expression are as follows:

  • Whether the current month is in:
    • The first half of the year (MyCurrentMonth <= 6); or
    • The second half of the year (MyCurrentMonth >= 7).
  • Whether the current day of the month is in:
    • The first half of the month (MyCurrentDay <= 15); or
    • The second half of the month (MyCurrentDay >= 16).

The image below displays the message box shown by Excel when I execute the Excel VBA Select Case And operator example macro. I execute this example macro (and create this screenshot) in the first half of August.

Excel VBA Select Case And operator example macro results


Get immediate free access to the Excel VBA Select Case And operator example workbook

Download the Excel VBA Select Case And Operator Example Workbook

This Excel VBA Select Case And Operator Tutorial is accompanied by an Excel workbook with the data and VBA code I use. Get this example workbook (for free) by clicking the button below.


Get immediate free access to the Excel VBA Select Case And operator example workbook

The VBA code in the Excel workbook that accompanies this Excel VBA Select Case And Operator Tutorial is (always) stored in the Visual Basic Editor (VBE). If you don't know how to work with the VBE, I suggest you read my Visual Basic Editor (VBE) Tutorial. I link to this Tutorial in the Related Excel Macro and VBA Training Materials and Resources Section.

Related Excel Macro and VBA Training Materials and Resources

The following Excel Macro and VBA Tutorials may help you better understand and implement this VBA Select Case And Operator Tutorial.

  • Tutorials about general macro and VBA constructs and structures:
    • Tutorials for Beginners:
      • Excel Macros: Click here to open.
      • Excel VBA: Click here to open.
    • Enable macros in Excel: Click here to open.
    • Work with the Visual Basic Editor (VBE): Click here to open.
    • Create Sub procedures: Click here to open.
    • Work with:
      • Variables: Click here to open.
      • Data types: Click here to open.
      • Functions: Click here to open.
  • Tutorials with practical VBA applications and macro examples:
    • MsgBox: Click here to open.

This Excel VBA Select Case And Operator Tutorial is part of a more comprehensive series of Excel VBA Select Case Tutorials.

  • Excel VBA Select Case Tutorial: Click here to open.
  • Excel VBA Select Case Or: Click here to open.
  • Excel VBA Select Case And Operator: Click here to open.
  • Excel VBA Select Case Multiple Test Expressions: Click here to open.
  • Excel VBA Select Case Like Wildcard: Click here to open.
  • Excel VBA Select Case Inside For… Next Loop: Click here to open.
  • Excel VBA Select Case Range of Cells: Click here to open.

You can find more Excel and VBA Tutorials in the organized Tutorials Archive: Click here to visit the Archives. The following are some of my most popular Excel Tutorials and Training Resources:

  • Excel Keyboard Shortcuts Cheat Sheet: Click here to open.
  • Work with the Excel XLOOKUP Function: Click here to open.
  • Excel Power Query (Get & Transform) Tutorial for Beginners: Click here to open.

If you want to learn how to automate Excel (and save time) by working with macros and VBA, you may be interested in the following Premium Excel Macro and VBA Training Materials:

  • Premium Courses at the Power Spreadsheets Academy: Click here to open.
  • Books at the Power Spreadsheets Library: Click here to open.
  • VBA Cheat Sheets: Click here to open.

If you want to save time when working with macros and VBA, you may be interested in AutoMacro: Click here to learn more about AutoMacro (affiliate link). AutoMacro is an add-in for VBA that installs directly into the VBE. Depending on the version, AutoMacro comes loaded with:

  • Code generators.
  • An extensive code library.
  • The ability to create your own code library.
  • Advanced coding tools.

If you need help with Excel tasks/projects, you may be interested in working with me: Click here to learn more about working with me.


Get immediate free access to the Excel VBA Select Case And operator example workbook

guest
guest
2 Comments
Most Voted
Newest Oldest
Inline Feedbacks
View all comments

I publish a lot of Tutorials and Training Resources about Microsoft Excel and VBA. Here are some of my most popular Excel Training Resources:

  1. Free Excel VBA Email Course
  2. Excel Macro Tutorial for Beginners
  3. Excel Power Query (Get and Transform) Tutorial for Beginners
  4. Excel Keyboard Shortcut Cheat Sheet
  5. Excel Resources
Terms and Conditions

Privacy Policy

Limit of Liability and Disclaimer of Warranty

Affiliate Disclosure

Copyright © 2015–2023 PDS Intelligence Pte. Ltd. All rights reserved.
Excel ® is a registered trademark of the Microsoft Corporation. Power Spreadsheets is not affiliated with the Microsoft Corporation.