Excel VBA Select Case Multiple Test Expressions in 5 Steps (+ Example)

In this Excel VBA Select Case Multiple Test Expressions Tutorial, you learn how to create an Excel VBA Select Case multiple test expressions statement to conditionally execute a set of statements based on an expression's value, where this expression considers multiple test expressions.

The Excel VBA Select Case multiple test expressions 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 Or.
Excel VBA Select Case And Operator.
Excel VBA Select Case Between 2 Values.
Excel VBA Select Case Multiple Values.
Excel VBA Select Case Multiple Conditions.

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

This Excel VBA Select Case Multiple Test Expressions 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.

The VBA code in the Excel workbook that accompanies this Excel VBA Select Case Multiple Test Expressions 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

The following Excel Macro and VBA Tutorials may help you better understand and implement this VBA Select Case Multiple Test Expressions 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 Multiple Test Expressions 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.

Excel VBA Select Case Multiple Test Expressions Snippet Template/Structure

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/select-case-test-expressions/
Select Case True
    Case LogicalCaseExpression1
        CaseStatements1
    Case LogicalCaseExpression2
        CaseStatements2
    '...
    Case LogicalCaseExpression#
        CaseStatements#
    Case Else
        ElseStatements
End Select

Step-by-Step Process to Create an Excel VBA Select Case Multiple Test Expressions 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/select-case-test-expressions/
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/select-case-test-expressions/
Select Case True
    '...
End Select

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

Case LogicalCaseExpression1.
Case LogicalCaseExpression2.
…
Case LogicalCaseExpression#.

To create a VBA Select Case Multiple Test Expressions statement, specify case expressions as logical expressions. Logical expressions return a Boolean value (True or False) when evaluated. Use logical operators (for example, And, Not, Or) to:

Build these logical expressions; and
Evaluate multiple conditions inside a single logical expression.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/select-case-test-expressions/
Select Case True
    Case LogicalCaseExpression1
        '...
    Case LogicalCaseExpression2
        '...
    '...
    Case LogicalCaseExpression#
        '...
    '...
End Select

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

CaseStatements1.
CaseStatements2.
…
CaseStatements#.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/select-case-test-expressions/
Select Case True
    Case LogicalCaseExpression1
        CaseStatements1
    Case LogicalCaseExpression2
        CaseStatements2
    '...
    Case LogicalCaseExpression#
        CaseStatements#
    '...
End Select

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

Follow the Case Else keyword; and
Are optional.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/select-case-test-expressions/
Select Case True
    Case LogicalCaseExpression1
        CaseStatements1
    Case LogicalCaseExpression2
        CaseStatements2
    '...
    Case LogicalCaseExpression#
        CaseStatements#
    Case Else
        ElseStatements
End Select

How (and Why) the VBA Select Case Multiple Test Expressions Statement Works

The Select Case statement does the following:

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

You (usually) use the Select Case statement to conditionally execute a set of statements, based on the test expression's value. You (as a general rule):

Specify a single test expression.
Don't specify multiple test expressions.

You can (however) work with logical expressions when specifying the case expressions inside the Select Case statement, to:

Work around this limitation (not being able to specify multiple test expressions); and
Achieve a similar practical effect (as specifying multiple test expressions).

To achieve this, do the following inside the applicable VBA Select Case multiple test expressions statement:

Set the test expression to the Boolean value True.
Specify the case expressions as logical expressions (expressions that return True or False). Use logical operators (for example, And, Not, Or) to:
- Build these logical expressions; and
- Evaluate multiple conditions inside a single logical expression.

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

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

Excel VBA Select Case Multiple Test Expressions Example Macro

The VBA Select Case multiple test expressions 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 week.

I use a VBA Select Case multiple test expressions statement to specify the proper message.

Sub SelectCaseMultipleTestExpressions()
    'Source: https://powerspreadsheets.com/
    'More information: https://powerspreadsheets.com/select-case-test-expressions/
    
    'Declare variables to represent:
        'Message to be displayed in message box
        'Current month (as a number between 1 and 12)
        'Current day of the week (as a number between 1 and 7)
    Dim MyMsgBoxMessage As String
    Dim MyCurrentMonth As Integer
    Dim MyCurrentWeekday As Integer
    
    'Identify current month and day of the week
    MyCurrentMonth = Month(Date:=Date)
    MyCurrentWeekday = Weekday(Date:=Date, FirstDayOfWeek:=vbMonday)
    
    'Assign a string to the MyMsgBoxMessage variable, depending on:
        'The current month; and
        'The current day of the week
    Select Case True
        Case (MyCurrentMonth <= 6) And (MyCurrentWeekday <= 5)
            MyMsgBoxMessage = "It's a weekday in the first half of the year"
        Case (MyCurrentMonth <= 6) And (MyCurrentWeekday >= 6)
            MyMsgBoxMessage = "It's a weekend in the first half of the year"
        Case (MyCurrentMonth >= 7) And (MyCurrentWeekday <= 5)
            MyMsgBoxMessage = "It's a weekday in the second half of the year"
        Case (MyCurrentMonth >= 7) And (MyCurrentWeekday >= 6)
            MyMsgBoxMessage = "It's a weekend 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 multiple test expressions statement inside the VBA Select Case multiple test expressions example macro specifies 4 case expressions as logical expressions. I use the And operator to:

Build these logical expressions; and
Evaluate 2 conditions inside each single logical expression.

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 week:
- Is a weekday (MyCurrentWeekday <= 5); or
- Falls on the weekend (MyCurrentWeekday >= 6).

The image below displays the message box shown by Excel when I execute the Excel VBA Select Case multiple test expressions example macro. I execute this example macro (and create this screenshot):

In a weekday;
Inside the first half of the year.

Download the Excel VBA Select Case Multiple Test Expressions Example Workbook