Excel VBA Select Case: Select Case Statement Example Macros

In this Excel VBA Select Case Tutorial, you learn how to use the Select Case statement in macros.

This Excel VBA Select Case Tutorial is currently under development. Subscribe to the Power Spreadsheets Newsletter and get future updates to this Excel VBA Select Case Tutorial.

This Excel VBA Select Case Tutorial is accompanied by an Excel workbook with the data and VBA code I use in the examples below. 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 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 below.

Table of Contents

The following Excel Macro and VBA Tutorials may help you better understand and implement the contents below.

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:
- Excel VBA Change Font Color Based on Cell Value: Click here to open.
- Create a message box: Click here to open.
- Create an input box: Click here to open.

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

(1) Excel VBA Select Case Without Case Else

In this Section, you learn how to create a basic Excel VBA Select Case statement (without the Case Else clause) to conditionally execute a set of statements based on an expression's value.

Excel VBA Select Case Without Case Else Snippet Template/Structure

The following is the Excel VBA Select Case Without Case Else snippet template/structure I explain (step-by-step) in the Sections below.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-vba-select-case/
Select Case TestExpression
    Case CaseExpression1
        CaseStatements1
    Case CaseExpression2
        CaseStatements2
    '...
    Case CaseExpression#
        CaseStatements#
End Select

Step-by-Step Process to Set Up an Excel VBA Select Case Without Case Else Statement

Do the following to create an Excel VBA Select Case Without Case Else 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/excel-vba-select-case/
Select Case '...
    '...
End Select

(2) Specify the test expression VBA uses to identify the set of statements to execute: TestExpression.

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

(3) Specify the case expressions used by VBA to identify the set of statements to execute:

CaseExpression1.
CaseExpression2.
…
CaseExpression#.

You can (as a general rule) include as many case expressions as required inside a single Select Case statement.

Each case expression is preceded by the Case keyword:

Case CaseExpression1.
Case CaseExpression2.
…
Case CaseExpression#.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-vba-select-case/
Select Case TestExpression
    Case CaseExpression1
        '...
    Case CaseExpression2
        '...
    '...
    Case CaseExpression#
        '...
End Select

The Case keyword plus associated case expression (Case CaseExpression#) form a Case clause.

When the test expression (you specified in step #2) matches an individual case expression, the Select Case statement:

Executes the set of statements (you specify in step #4) associated to the applicable Case clause (whose case expression matched the test expression); and
Exits the Select Case statement.

(4) Specify the set of statements to be executed when the applicable case expression (you specified in step #3) matches the test expression (you specified in step #2).

CaseStatements1.
CaseStatements2.
…
CaseStatements#.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-vba-select-case/
Select Case TestExpression
    Case CaseExpression1
        CaseStatements1
    Case CaseExpression2
        CaseStatements2
    '...
    Case CaseExpression#
        CaseStatements#
End Select

Additional Cues for Excel VBA Select Case Without Case Else

(1) The Select Case statement is an alternative to complex If… Then… Else statements (Select Case vs. If… Then… Else). Consider using the Select Case statement when dealing with (more) complex cases.

(2) Consider indenting the statements inside Select Case statements.

(3) Consider working with the separator character (:) to separate the Case clause (Case CaseExpression#) and the statements to be executed (CaseStatements#), when the following 2 conditions are met:

The case results in a single statement (CaseStatement#) being executed; and
The resulting line of VBA code is not excessively long.

(4) You have a significant degree of flexibility when specifying case expressions inside a Select Case statement.

Please refer to the Section on Excel VBA Select Case Multiple Conditions (in this Excel VBA Select Case Tutorial) for a more detailed explanation on how to create a Select Case statement to conditionally execute a set of statements based on multiple conditions (grouping several cases inside a single case expression).

(5) As a general rule: Organize case expressions:

By decreasing order of probability.
In such a way that any case expression excludes subsequent case expressions.

(6) The Select Case statement contains a set of catch-all statements executed if no case expression matches the test expression. These statements follow the Case Else keyword. The Case Else keyword (and clause) is optional.

Excel VBA Select Case Without Case Else Example Macro

This Excel VBA Select Case Tutorial is accompanied by an Excel workbook with the data and VBA code I use in the examples inside this Tutorial (including this Select Case Without Case Else example). Get this example workbook (for free) by clicking the button below.

I create the Excel VBA Select Case without Case Else example macro as follows:

(1) Declare a Sub procedure (SelectCaseWithoutCaseElse).

Sub SelectCaseWithoutCaseElse()
    'Source: https://powerspreadsheets.com/
    'More information: https://powerspreadsheets.com/excel-vba-select-case/
    
    '...
    
End Sub

(2) Declare a variable (MyWeekdayMessage) of the String data type.

Sub SelectCaseWithoutCaseElse()
    'Source: https://powerspreadsheets.com/
    'More information: https://powerspreadsheets.com/excel-vba-select-case/

    'Declare variable to represent message to be displayed in message box
    Dim MyWeekdayMessage As String

    '...
    
End Sub

(3) Enter the opening and closing statements of the Select Case statement.

Sub SelectCaseWithoutCaseElse()
    'Source: https://powerspreadsheets.com/
    'More information: https://powerspreadsheets.com/excel-vba-select-case/

    'Declare variable to represent message to be displayed in message box
    Dim MyWeekdayMessage As String

    Select Case '...
        '...
    End Select
    
    '...
    
End Sub

(4) Specify the test expression VBA uses to identify the set of statements to execute.

The test expression I use (Weekday(Date:=Date, FirstDayOfWeek:=vbMonday)) returns an integer (1 to 7) representing the current day of the week.

Sub SelectCaseWithoutCaseElse()
    'Source: https://powerspreadsheets.com/
    'More information: https://powerspreadsheets.com/excel-vba-select-case/

    'Declare variable to represent message to be displayed in message box
    Dim MyWeekdayMessage As String

    Select Case Weekday(Date:=Date, FirstDayOfWeek:=vbMonday)
        '...
    End Select
    
    '...
    
End Sub

(5) Specify the case expressions used by VBA to identify the set of statements to execute.

The test expression I specified in step #4 returns an integer between 1 and 7. The case expressions I specify are (therefore):

Sub SelectCaseWithoutCaseElse()
    'Source: https://powerspreadsheets.com/
    'More information: https://powerspreadsheets.com/excel-vba-select-case/

    'Declare variable to represent message to be displayed in message box
    Dim MyWeekdayMessage As String

    Select Case Weekday(Date:=Date, FirstDayOfWeek:=vbMonday)
        Case 1: '...
        Case 2: '...
        Case 3: '...
        Case 4: '...
        Case 5: '...
        Case 6: '...
        Case 7: '...
    End Select
    
    '...
    
End Sub

(6) Specify the statement to be executed when the applicable case expression (I specified in step #5) matches the test expression (I specified in step #4).

The statements I use assign a string to the MyWeekdayMessage variable (I declared in step #2). The assigned string varies, depending on the applicable case expression (I specified in step #5):

Case expression is 1: Assigned string is “It's Monday”.
Case expression is 2: Assigned string is “It's Tuesday”.
Case expression is 3: Assigned string is “It's Wednesday”.
Case expression is 4: Assigned string is “It's Thursday”.
Case expression is 5: Assigned string is “It's Friday”.
Case expression is 6: Assigned string is “It's Saturday”.
Case expression is 7: Assigned string is “It's Sunday”.

Sub SelectCaseWithoutCaseElse()
    'Source: https://powerspreadsheets.com/
    'More information: https://powerspreadsheets.com/excel-vba-select-case/

    'Declare variable to represent message to be displayed in message box
    Dim MyWeekdayMessage As String

    'Assign a string to the MyWeekdayMessage variable, depending on the current day of the week
    Select Case Weekday(Date:=Date, FirstDayOfWeek:=vbMonday)
        Case 1: MyWeekdayMessage = "It's Monday"
        Case 2: MyWeekdayMessage = "It's Tuesday"
        Case 3: MyWeekdayMessage = "It's Wednesday"
        Case 4: MyWeekdayMessage = "It's Thursday"
        Case 5: MyWeekdayMessage = "It's Friday"
        Case 6: MyWeekdayMessage = "It's Saturday"
        Case 7: MyWeekdayMessage = "It's Sunday"
    End Select
    
    '...
    
End Sub

(6) Display a message box (MsgBox) with the value held by the MyWeekdayMessage variable.

Sub SelectCaseWithoutCaseElse()
    'Source: https://powerspreadsheets.com/
    'More information: https://powerspreadsheets.com/excel-vba-select-case/
    
    'Declare variable to represent message to be displayed in message box
    Dim MyWeekdayMessage As String
    
    'Assign a string to the MyWeekdayMessage variable, depending on the current day of the week
    Select Case Weekday(Date:=Date, FirstDayOfWeek:=vbMonday)
        Case 1: MyWeekdayMessage = "It's Monday"
        Case 2: MyWeekdayMessage = "It's Tuesday"
        Case 3: MyWeekdayMessage = "It's Wednesday"
        Case 4: MyWeekdayMessage = "It's Thursday"
        Case 5: MyWeekdayMessage = "It's Friday"
        Case 6: MyWeekdayMessage = "It's Saturday"
        Case 7: MyWeekdayMessage = "It's Sunday"
    End Select
    
    'Display message box with string held by the MyWeekdayMessage variable
    MsgBox MyWeekdayMessage
    
End Sub

The image below displays the message box shown by Excel when I execute the Excel VBA Select Case without Case Else example macro. I execute the example macro (and create this screenshot) on a Sunday.

Excel VBA Select Case Without Case Else example macro results

Select Case Without Case Else vs. If… Then… ElseIf… Example Macro

The following If… Then… ElseIf… example macro is an equivalent example macro (to the Select Case without Case Else example macro I explain above) working with the If… Then… Else statement (vs. the Select Case statement).

Sub SelectCaseWithoutCaseElseVsIfThenElseIf()
    'Source: https://powerspreadsheets.com/
    'More information: https://powerspreadsheets.com/excel-vba-select-case/
    
    'Declare variables to represent:
        '(1) Number representing day of the week
        '(2) Message to be displayed in message box
    Dim MyWeekday As Integer
    Dim MyWeekdayMessage As String
    
    'Assign number representing day of the week to the MyWeekday variable
    MyWeekday = Weekday(Date:=Date, FirstDayOfWeek:=vbMonday)
    
    'Assign a string to the MyWeekdayMessage variable, depending on the current day of the week
    If MyWeekday = 1 Then
        MyWeekdayMessage = "It's Monday"
    ElseIf MyWeekday = 2 Then
        MyWeekdayMessage = "It's Tuesday"
    ElseIf MyWeekday = 3 Then
        MyWeekdayMessage = "It's Wednesday"
    ElseIf MyWeekday = 4 Then
        MyWeekdayMessage = "It's Thursday"
    ElseIf MyWeekday = 5 Then
        MyWeekdayMessage = "It's Friday"
    ElseIf MyWeekday = 6 Then
        MyWeekdayMessage = "It's Saturday"
    ElseIf MyWeekday = 7 Then
        MyWeekdayMessage = "It's Sunday"
    End If
    
    'Display message box with string held by the MyWeekdayMessage variable
    MsgBox MyWeekdayMessage
    
End Sub

Notice how the Select Case statement results in more efficient VBA code that's easier to:

Read;
Follow;
Understand; and
Work with.

The image below displays the message box shown by Excel when I execute the Excel VBA If… Then… ElseIf… example macro. I execute the example macro (and create this screenshot) on a Sunday.

Excel VBA Select Case Without Case Else vs. If... Then... ElseIf... example macro results

(2) Excel VBA Select Case Else

In this Section, you learn how to create a basic Excel VBA Select Case statement (with the Case Else clause) to:

Conditionally execute a set of statements based on an expression's value; and
Specify a set of catch-all statements that are executed when none of the conditions you (originally) expected is met.

Excel VBA Select Case Else Snippet Template/Structure

The following is the Excel VBA Select Case Else snippet template/structure I explain (step-by-step) in the Sections below.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-vba-select-case/
Select Case TestExpression
    Case CaseExpression1
        CaseStatements1
    Case CaseExpression2
        CaseStatements2
    '...
    Case CaseExpression#
        CaseStatements#
    Case Else
        ElseStatements
End Select

Step-by-Step Process to Set Up an Excel VBA Select Case Else Statement

Do the following to create an Excel VBA Select Case Else 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/excel-vba-select-case/
Select Case '...
    '...
End Select

(2) Specify the test expression VBA uses to identify the set of statements to execute: TestExpression.

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

(3) Specify the case expressions used by VBA to identify the set of statements to execute:

CaseExpression1.
CaseExpression2.
…
CaseExpression#.

You can (as a general rule) include as many case expressions as required inside a single Select Case statement.

Each case expression is preceded by the Case keyword:

Case CaseExpression1.
Case CaseExpression2.
…
Case CaseExpression#.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-vba-select-case/
Select Case TestExpression
    Case CaseExpression1
        '...
    Case CaseExpression2
        '...
    '...
    Case CaseExpression#
        '...
    '...
End Select

The Case keyword plus associated case expression (Case CaseExpression#) form a Case clause.

When the test expression (you specified in step #2) matches an individual case expression, the Select Case statement:

Executes the set of statements (you specify in step #4) associated to the applicable Case clause (whose case expression matched the test expression); and
Exits the Select Case statement.

(4) Specify the set of statements to be executed when the applicable case expression (you specified in step #3) matches the test expression (you specified in step #2).

CaseStatements1.
CaseStatements2.
…
CaseStatements#.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-vba-select-case/
Select Case TestExpression
    Case CaseExpression1
        CaseStatements1
    Case CaseExpression2
        CaseStatements2
    '...
    Case CaseExpression#
        CaseStatements#
    '...
End Select

(5) Specify the set of statements to be executed if no case expression (you specified in step #3) matches the test expression (you specified in step #2). These catch-all statements follow the Case Else keyword.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-vba-select-case/
Select Case TestExpression
    Case CaseExpression1
        CaseStatements1
    Case CaseExpression2
        CaseStatements2
    '...
    Case CaseExpression#
        CaseStatements#
    Case Else
        ElseStatements
End Select

Additional Cues for Excel VBA Select Case Else

(2) Consider indenting the statements inside Select Case statements.

(3) Consider working with the separator character (:) to separate:

The Case clause (Case CaseExpression#) and the statements to be executed (CaseStatements#); or
The Case Else keyword (Case Else) and the catch-all statements (ElseStatements);

When the following 2 conditions are met:

The case results in a single statement (CaseStatement#) being executed or there's (only) a single catch-all statement (ElseStatement); and
The resulting line of VBA code is not excessively long.

(3) You have a significant degree of flexibility when specifying case expressions inside a Select Case statement.

(4) As a general rule: Organize case expressions:

By decreasing order of probability.
In such a way that any case expression excludes subsequent case expressions.

(5) The Case Else keyword/clause (Case Else: ElseStatements) is optional.

Work with the Case Else clause to specify a set of catch-all statements that are executed when none of the conditions you (originally) expected is met (no case expression matches the test expression).

(6) If:

You omit the Case Else clause; and
No case expression matches the test expression;

Procedure execution continues with the first statement after the end of the Select Case statement (after “End Select”).

Excel VBA Select Case Else Example Macro

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

I create the Excel VBA Select Case Else example macro as follows:

(1) Declare a Sub procedure (SelectCaseElse).

Sub SelectCaseElse()
    'Source: https://powerspreadsheets.com/
    'More information: https://powerspreadsheets.com/excel-vba-select-case/
    
    '…
    
End Sub

(2) Declare a variable (MyMonthPartMessage) of the String data type.

Sub SelectCaseElse()
    'Source: https://powerspreadsheets.com/
    'More information: https://powerspreadsheets.com/excel-vba-select-case/
    
    'Declare variable to represent message to be displayed in message box
    Dim MyMonthPartMessage As String
    
    '…
    
End Sub

(3) Enter the opening and closing statements of the Select Case statement.

Sub SelectCaseElse()
    'Source: https://powerspreadsheets.com/
    'More information: https://powerspreadsheets.com/excel-vba-select-case/
    
    'Declare variable to represent message to be displayed in message box
    Dim MyMonthPartMessage As String
    
    Select Case '…
        '…
    End Select
    
    '…
    
End Sub

(4) Specify the test expression VBA uses to identify the set of statements to execute.

The test expression I use (Day(Date:=Date)) returns an integer (1 to 31) representing the current day of the month.

Sub SelectCaseElse()
    'Source: https://powerspreadsheets.com/
    'More information: https://powerspreadsheets.com/excel-vba-select-case/
    
    'Declare variable to represent message to be displayed in message box
    Dim MyMonthPartMessage As String
    
    Select Case Day(Date:=Date)
        '…
    End Select
    
    '…
    
End Sub

(5) Specify the case expressions used by VBA to identify the set of statements to execute.

The test expression I specified in step #4 returns an integer between 1 and 31. The case expressions I specify are (therefore):

1 To 5.
6 To 10.
11 To 15.
16 To 20.
21 To 25.

Sub SelectCaseElse()
    'Source: https://powerspreadsheets.com/
    'More information: https://powerspreadsheets.com/excel-vba-select-case/
    
    'Declare variable to represent message to be displayed in message box
    Dim MyMonthPartMessage As String
    
    Select Case Day(Date:=Date)
        Case 1 To 5: '…
        Case 6 To 10: '…
        Case 11 To 15: '…
        Case 16 To 20: '…
        Case 21 To 25: '…
        '…
    End Select
    
    '…
    
End Sub

(6) Specify the statement to be executed when the applicable case expression (I specified in step #5) matches the test expression (I specified in step #4).

The statements I use assign a string to the MyMonthPartMessage variable (I declared in step #2). The assigned string varies, depending on the applicable case expression (I specified in step #5):

Case expression is 1 to 5 (both inclusive): Assigned string is “We're between the first and the fifth of the month”.
Case expression is 6 to 10 (both inclusive): Assigned string is “We're between the sixth and the tenth of the month”.
Case expression is 11 to 15 (both inclusive): Assigned string is “We're between the eleventh and the fifteenth of the month”.
Case expression is 16 to 20 (both inclusive): Assigned string is “We're between the sixteenth and the twentieth of the month”.
Case expression is 21 to 25 (both inclusive): Assigned string is “We're between the twenty-first and the twenty-fifth of the month”.

Sub SelectCaseElse()
    'Source: https://powerspreadsheets.com/
    'More information: https://powerspreadsheets.com/excel-vba-select-case/
    
    'Declare variable to represent message to be displayed in message box
    Dim MyMonthPartMessage As String
    
    'Assign a string to the MyMonthPartMessage variable, depending on the current day of the month
    Select Case Day(Date:=Date)
        Case 1 To 5: MyMonthPartMessage = "We're between the first and the fifth of the month"
        Case 6 To 10: MyMonthPartMessage = "We're between the sixth and the tenth of the month"
        Case 11 To 15: MyMonthPartMessage = "We're between the eleventh and the fifteenth of the month"
        Case 16 To 20: MyMonthPartMessage = "We're between the sixteenth and the twentieth of the month"
        Case 21 To 25: MyMonthPartMessage = "We're between the twenty-first and the twenty-fifth of the month"
        '…
    End Select
    
    '…
    
End Sub

(7) Specify the catch-all statement to be executed if no case expression (from the case expressions I specified in step #5) matches the test expression (I specified in step #4).

The statement I use assigns a string to the MyMonthPartMessage variable (I declared in step #2): “We're past the twenty-fifth of the month”.

Sub SelectCaseElse()
    'Source: https://powerspreadsheets.com/
    'More information: https://powerspreadsheets.com/excel-vba-select-case/
    
    'Declare variable to represent message to be displayed in message box
    Dim MyMonthPartMessage As String
    
    'Assign a string to the MyMonthPartMessage variable, depending on the current day of the month
    Select Case Day(Date:=Date)
        Case 1 To 5: MyMonthPartMessage = "We're between the first and the fifth of the month"
        Case 6 To 10: MyMonthPartMessage = "We're between the sixth and the tenth of the month"
        Case 11 To 15: MyMonthPartMessage = "We're between the eleventh and the fifteenth of the month"
        Case 16 To 20: MyMonthPartMessage = "We're between the sixteenth and the twentieth of the month"
        Case 21 To 25: MyMonthPartMessage = "We're between the twenty-first and the twenty-fifth of the month"
        Case Else: MyMonthPartMessage = "We're past the twenty-fifth of the month"
    End Select
    
    '…
    
End Sub

(8) Display a message box (MsgBox) with the value held by the MyMonthPartMessage variable.

Sub SelectCaseElse()
    'Source: https://powerspreadsheets.com/
    'More information: https://powerspreadsheets.com/excel-vba-select-case/
    
    'Declare variable to represent message to be displayed in message box
    Dim MyMonthPartMessage As String
    
    'Assign a string to the MyMonthPartMessage variable, depending on the current day of the month
    Select Case Day(Date:=Date)
        Case 1 To 5: MyMonthPartMessage = "We're between the first and the fifth of the month"
        Case 6 To 10: MyMonthPartMessage = "We're between the sixth and the tenth of the month"
        Case 11 To 15: MyMonthPartMessage = "We're between the eleventh and the fifteenth of the month"
        Case 16 To 20: MyMonthPartMessage = "We're between the sixteenth and the twentieth of the month"
        Case 21 To 25: MyMonthPartMessage = "We're between the twenty-first and the twenty-fifth of the month"
        Case Else: MyMonthPartMessage = "We're past the twenty-fifth of the month"
    End Select
    
    'Display message box with string held by the MyMonthPartMessage variable
    MsgBox MyMonthPartMessage
    
End Sub

The image below displays the message box shown by Excel when I execute the Excel VBA Select Case Else example macro. I execute the example macro (and create this screenshot) on day 13 of the month.

Excel VBA Select Case Else example macro results

Select Case Else vs. If… Then… ElseIf… Else Example Macro

The following If… Then… ElseIf… Else example macro is an equivalent example macro (to the Select Case Else example macro I explain above) working with the If… Then… Else statement (vs. the Select Case statement).

Sub SelectCaseElseVsIfThenElseIfElse()
    'Source: https://powerspreadsheets.com/
    'More information: https://powerspreadsheets.com/excel-vba-select-case/
    
    'Declare variables to represent:
        '(1) Number representing day of the month
        '(2) Message to be displayed in message box
    Dim MyMonthDay As Integer
    Dim MyMonthPartMessage As String
    
    'Assign number representing day of the month to the MyMonthDay variable
    MyMonthDay = Day(Date:=Date)
    
    'Assign a string to the MyMonthPartMessage variable, depending on the current day of the month
    If MyMonthDay <= 5 Then
        MyMonthPartMessage = "We're between the first and the fifth of the month"
    ElseIf (MyMonthDay >= 6) And (MyMonthDay <= 10) Then
        MyMonthPartMessage = "We're between the sixth and the tenth of the month"
    ElseIf (MyMonthDay >= 11) And (MyMonthDay <= 15) Then
        MyMonthPartMessage = "We're between the eleventh and the fifteenth of the month"
    ElseIf (MyMonthDay >= 16) And (MyMonthDay <= 20) Then
        MyMonthPartMessage = "We're between the sixteenth and the twentieth of the month"
    ElseIf (MyMonthDay >= 21) And (MyMonthDay <= 25) Then
        MyMonthPartMessage = "We're between the twenty-first and the twenty-fifth of the month"
    Else
        MyMonthPartMessage = "We're past the twenty-fifth of the month"
    End If
    
    'Display message box with string held by the MyMonthPartMessage variable
    MsgBox MyMonthPartMessage
    
End Sub

Notice how the Select Case statement results in more efficient VBA code that's easier to:

Read;
Follow;
Understand; and
Work with.

The image below displays the message box shown by Excel when I execute the Excel VBA If… Then… ElseIf… Else example macro. I execute the example macro (and create this screenshot) on day 13 of the month.

Excel VBA Select Case vs. If... Then... ElseIf... Else example macro results

(3) Excel VBA Select Case String

In this Section, you learn how to create an Excel VBA Select Case string statement, to conditionally execute a set of statements based on a string.

Excel VBA Select Case String Formula/Snippet Template/Structure

The following is the Excel VBA Select Case string snippet template/structure I explain (step-by-step) in the Sections below.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-vba-select-case/
Select Case StringTestExpression
    Case StringCaseExpression1
        CaseStatements1
    Case StringCaseExpression2
        CaseStatements2
    '...
    Case StringCaseExpression#
        CaseStatements#
    Case Else
        ElseStatements
End Select

Step-by-Step Process to Set Up an Excel VBA Select Case String Statement

Do the following to create an Excel VBA Select Case string 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/excel-vba-select-case/
Select Case '...
    '...
End Select

(2) Specify the string test expression VBA uses to identify the set of statements to execute: StringTestExpression.

To create an Excel VBA Select Case string statement, specify this string test expression as:

A string; or
A string expression.

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

(3) Specify the string case expressions used by VBA to identify the set of statements to execute:

StringCaseExpression1.
StringCaseExpression2.
…
StringCaseExpression#.

You can (as a general rule) include as many string case expressions as required inside a single Select Case statement.

Each string case expression is preceded by the Case keyword:

Case StringCaseExpression1.
Case StringCaseExpression2.
…
Case StringCaseExpression#.

To create an Excel VBA Select Case string statement, specify case expressions as:

A string; or
A string expression.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-vba-select-case/
Select Case StringTestExpression
    Case StringCaseExpression1
        '...
    Case StringCaseExpression2
        '...
    '...
    Case StringCaseExpression#
        '...
    '...
End Select

The Case keyword plus associated string case expression (Case StringCaseExpression#) form a Case clause.

When the string test expression (you specified in step #2) matches an individual string case expression, the Select Case statement:

Executes the set of statements (you specify in step #4) associated to the applicable Case clause (whose string case expression matched the string test expression); and
Exits the Select Case statement.

(4) Specify the set of statements to be executed when the applicable string case expression (you specified in step #3) matches the string test expression (you specified in step #2).

CaseStatements1.
CaseStatements2.
…
CaseStatements#.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-vba-select-case/
Select Case StringTestExpression
    Case StringCaseExpression1
        CaseStatements1
    Case StringCaseExpression2
        CaseStatements2
    '...
    Case StringCaseExpression#
        CaseStatements#
    '...
End Select

(5) Specify the set of statements to be executed if no string case expression (you specified in step #3) matches the string test expression (you specified in step #2). These catch-all statements:

Follow the Case Else keyword; and
Are optional.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-vba-select-case/
Select Case StringTestExpression
    Case StringCaseExpression1
        CaseStatements1
    Case StringCaseExpression2
        CaseStatements2
    '...
    Case StringCaseExpression#
        CaseStatements#
    Case Else
        ElseStatements
End Select

Additional Cues for Excel VBA Select Case String

(2) Consider indenting the statements inside Select Case statements.

(3) Consider working with the separator character (:) to separate:

The Case clause (Case StringCaseExpression#) and the statements to be executed (CaseStatements#); or
The Case Else keyword (Case Else) and the catch-all statements (ElseStatements);

When the following 2 conditions are met:

The case results in a single statement (CaseStatement#) being executed or there's (only) a single catch-all statement (ElseStatement); and
The resulting line of VBA code is not excessively long.

(3) You have a significant degree of flexibility when specifying case expressions inside a Select Case statement. This flexibility includes the possibility to group several cases inside a single case expression.

(5) Use the Option Compare statement (at the module level) to specify the default comparison method VBA uses when comparing strings:

Option Compare Binary; or
Option Compare Text.

Option Compare Binary results in a case sensitive comparison. Option Compare Text results in a case insensitive comparison.

The default string comparison method is binary.

(6) As a general rule: Organize case expressions:

By decreasing order of probability.
In such a way that any case expression excludes subsequent case expressions.

(7) The Case Else keyword/clause (Case Else: ElseStatements) is optional.

Work with the Case Else clause to specify a set of catch-all statements that are executed when none of the conditions you (originally) expected is met (no string case expression matches the string test expression).

(8) If:

You omit the Case Else clause; and
No string case expression matches the string test expression;

Procedure execution continues with the first statement after the end of the Select Case statement (after “End Select”).

Excel VBA Select Case String Example Macro

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

I create the Excel VBA Select Case string example macro as follows:

(1) Declare a Sub procedure (SelectCaseString).

Sub SelectCaseString()
    'Source: https://powerspreadsheets.com/
    'More information: https://powerspreadsheets.com/excel-vba-select-case/
    
    '...
    
End Sub

(2) Declare a variable (MyQuarterMessage) of the String data type.

Sub SelectCaseString()
    'Source: https://powerspreadsheets.com/
    'More information: https://powerspreadsheets.com/excel-vba-select-case/
    
    'Declare variable to represent message to be displayed in message box
    Dim MyQuarterMessage As String
    
    '...
    
End Sub

(3) Enter the opening and closing statements of the Select Case statement.

Sub SelectCaseString()
    'Source: https://powerspreadsheets.com/
    'More information: https://powerspreadsheets.com/excel-vba-select-case/
    
    'Declare variable to represent message to be displayed in message box
    Dim MyQuarterMessage As String
    
    Select Case '...
        '...
    End Select
    
    '...
    
End Sub

(4) Specify the string test expression VBA uses to identify the set of statements to execute.

The test expression I use (MonthName(Month(Date:=Date))) returns the current month's name.

Sub SelectCaseString()
    'Source: https://powerspreadsheets.com/
    'More information: https://powerspreadsheets.com/excel-vba-select-case/
    
    'Declare variable to represent message to be displayed in message box
    Dim MyQuarterMessage As String
    
    Select Case MonthName(Month(Date:=Date))
        '...
    End Select
    
    '...
    
End Sub

(5) Specify the string case expressions used by VBA to identify the set of statements to execute.

The string test expression I specified in step #4 returns a month's name (“January” to “December”). Additionally, I use commas to separate multiple strings inside a single string case expression.

The string case expressions I specify are (therefore):

“January”, “February”, “March”.
“April”, “May”, “June”.
“July”, “August”, “September”.
“October”, “November”, “December”.

Sub SelectCaseString()
    'Source: https://powerspreadsheets.com/
    'More information: https://powerspreadsheets.com/excel-vba-select-case/
    
    'Declare variable to represent message to be displayed in message box
    Dim MyQuarterMessage As String

    Select Case MonthName(Month(Date:=Date))
        Case "January", "February", "March" '...
        Case "April", "May", "June" '...
        Case "July", "August", "September" '...
        Case "October", "November", "December" '...
    End Select
    
    '...
    
End Sub

(6) Specify the statement to be executed when the applicable string case expression (I specified in step #5) matches the string test expression (I specified in step #4).

The statements I use assign a string to the MyQuarterMessage variable (I declared in step #2). The assigned string varies, depending on the applicable string case expression (I specified in step #5):

String case expression is “January”, “February”, “March”: Assigned string is “It's Q1”.
String case expression is “April”, “May”, “June”: Assigned string is “It's Q2”.
String case expression is “July”, “August”, “September”: Assigned string is “It's Q3”.
String case expression is “October”, “November”, “December”: Assigned string is “It's Q4”.

Sub SelectCaseString()
    'Source: https://powerspreadsheets.com/
    'More information: https://powerspreadsheets.com/excel-vba-select-case/
    
    'Declare variable to represent message to be displayed in message box
    Dim MyQuarterMessage As String
    
    'Assign a string to the MyQuarterMessage variable, depending on the current month
    Select Case MonthName(Month(Date:=Date))
        Case "January", "February", "March": MyQuarterMessage = "It's Q1"
        Case "April", "May", "June": MyQuarterMessage = "It's Q2"
        Case "July", "August", "September": MyQuarterMessage = "It's Q3"
        Case "October", "November", "December": MyQuarterMessage = "It's Q4"
    End Select
    
    '...
    
End Sub

(7) Display a message box (MsgBox) with the value held by the MyQuarterMessage variable.

Sub SelectCaseString()
    'Source: https://powerspreadsheets.com/
    'More information: https://powerspreadsheets.com/excel-vba-select-case/
    
    'Declare variable to represent message to be displayed in message box
    Dim MyQuarterMessage As String
    
    'Assign a string to the MyQuarterMessage variable, depending on the current month
    Select Case MonthName(Month(Date:=Date))
        Case "January", "February", "March": MyQuarterMessage = "It's Q1"
        Case "April", "May", "June": MyQuarterMessage = "It's Q2"
        Case "July", "August", "September": MyQuarterMessage = "It's Q3"
        Case "October", "November", "December": MyQuarterMessage = "It's Q4"
    End Select
    
    'Display message box with string held by the MyQuarterMessage variable
    MsgBox MyQuarterMessage
    
End Sub

The image below displays the message box shown by Excel when I execute the Excel VBA Select Case string example macro. I execute the example macro (and create this screenshot) during April.

Excel VBA Select Case string example macro results

Select Case String vs. If… Then… Else String Example Macro

The following If… Then… Else string example macro is an equivalent example macro (to the Select Case string example macro I explain above) working with the If… Then… Else statement (vs. the Select Case statement).

Sub SelectCaseStringVsIfThenElseIfString()
    'Source: https://powerspreadsheets.com/
    'More information: https://powerspreadsheets.com/excel-vba-select-case/
    
    'Declare variables to represent:
        '(1) Month name
        '(2) Message to be displayed in message box
    Dim MyMonth As String
    Dim MyQuarterMessage As String
    
    'Assign current month's name to the MyMonth variable
    MyMonth = MonthName(Month(Date:=Date))
    
    'Assign a string to the MyQuarterMessage variable, depending on the current month
    If (MyMonth = "January") Or (MyMonth = "February") Or (MyMonth = "March") Then
        MyQuarterMessage = "It's Q1"
    ElseIf (MyMonth = "April") Or (MyMonth = "May") Or (MyMonth = "June") Then
        MyQuarterMessage = "It's Q2"
    ElseIf (MyMonth = "July") Or (MyMonth = "August") Or (MyMonth = "September") Then
        MyQuarterMessage = "It's Q3"
    ElseIf (MyMonth = "October") Or (MyMonth = "November") Or (MyMonth = "December") Then
        MyQuarterMessage = "It's Q4"
    End If
    
    'Display message box with string held by the MyQuarterMessage variable
    MsgBox MyQuarterMessage
    
End Sub

Notice how the Select Case statement results in more efficient VBA code that's easier to:

Read;
Follow;
Understand; and
Work with.

The image below displays the message box shown by Excel when I execute the Excel VBA If… Then… Else string example macro. I execute the example macro (and create this screenshot) during April.

Excel VBA Select Case string vs. If... Then... Else string example macro results

(4) Excel VBA Select Case Between 2 Values

In this Section, you learn how to create an Excel VBA Select Case between 2 values statement to conditionally execute a set of statements based on whether an expression returns a value between 2 (other) values.

Excel VBA Select Case Between 2 Values Snippet Template/Structure

The following is the Excel VBA Select Case between 2 values snippet template/structure I explain (step-by-step) in the Sections below.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-vba-select-case/
Select Case NumericTestExpression
    Case LowerBoundCase1 To UpperBoundCase1
        CaseStatements1
    Case LowerBoundCase2 To UpperBoundCase2
        CaseStatements2
    '...
    Case LowerBoundCase# To UpperBoundCase#
        CaseStatements#
    Case Else
        ElseStatements
End Select

Step-by-Step Process to Set Up an Excel VBA Select Case Between 2 Values Statement

Do the following to create an Excel VBA Select Case between 2 values 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/excel-vba-select-case/
Select Case '...
    '...
End Select

(2) Specify the test expression VBA uses to identify the set of statements to execute, as a numeric expression (an expression that can be evaluated as a number): NumericTestExpression.

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

(3) Specify the numeric case expressions used by VBA to identify the set of statements to execute. Use the To keyword to specify a range of values for each single case expression.

LowerBoundCase1 To UpperBoundCase1.
LowerBoundCase2 To UpperBoundCase2.
…
LowerBoundCase# To UpperBoundCase#.

For these purposes:

LowerBoundCase# is the smaller value in the specified range of values; and
UpperBoundCase# is the larger value in the specified range of values.

You can (as a general rule) include as many case expressions as required inside a single Select Case statement.

Each case expression is preceded by the Case keyword:

Case LowerBoundCase1 To UpperBoundCase1.
Case LowerBoundCase2 To UpperBoundCase2.
…
Case LowerBoundCase# To UpperBoundCase#.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-vba-select-case/
Select Case NumericTestExpression
    Case LowerBoundCase1 To UpperBoundCase1
        '...
    Case LowerBoundCase2 To UpperBoundCase2
        '...
    '...
    Case LowerBoundCase# To UpperBoundCase#
        '...
    '...
End Select

The Case keyword plus associated case expression (Case LowerBoundCase# To UpperBoundCase#) form a Case clause.

When the value returned by the numeric test expression (you specified in step #2) is between a case expression's 2 values, the Select Case statement:

Executes the set of statements (you specify in step #4) associated to the applicable Case clause (whose case expression matched the test expression); and
Exits the Select Case statement.

(4) Specify the set of statements to be executed when the value returned by the numeric test expression (you specified in step #2) is between a case expression's 2 values (you specified in step #3).

CaseStatements1.
CaseStatements2.
…
CaseStatements#.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-vba-select-case/
Select Case NumericTestExpression
    Case LowerBoundCase1 To UpperBoundCase1
        CaseStatements1
    Case LowerBoundCase2 To UpperBoundCase2
        CaseStatements2
    '...
    Case LowerBoundCase# To UpperBoundCase#
        CaseStatements#
    '...
End Select

(5) Specify the set of statements to be executed if the value returned by the numeric test expression (you specified in step #2) isn't between any case expression's 2 values (you specified in step #3). These catch-all statements follow the Case Else keyword.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-vba-select-case/
Select Case NumericTestExpression
    Case LowerBoundCase1 To UpperBoundCase1
        CaseStatements1
    Case LowerBoundCase2 To UpperBoundCase2
        CaseStatements2
    '...
    Case LowerBoundCase# To UpperBoundCase#
        CaseStatements#
    Case Else
        ElseStatements
End Select

Additional Cues for Excel VBA Select Case Between 2 Values

(2) Consider indenting the statements inside Select Case statements.

(3) Consider working with the separator character (:) to separate:

The Case clause (Case LowerBoundCase# To UpperBoundCase#) and the statements to be executed (CaseStatements#); or
The Case Else keyword (Case Else) and the catch-all statements (ElseStatements);

When the following 2 conditions are met:

The case results in a single statement (CaseStatement#) being executed or there's (only) a single catch-all statement (ElseStatement); and
The resulting line of VBA code is not excessively long.

(3) You have a significant degree of flexibility when specifying case expressions inside a Select Case statement. This flexibility includes the possibility to specify a range of values for a single case expression (as you learned in this Section).

(4) As a general rule: Organize case expressions:

By decreasing order of probability.
In such a way that any case expression excludes subsequent case expressions.

(5) The Case Else keyword/clause (Case Else: ElseStatements) is optional.

(6) If:

You omit the Case Else clause; and
No case expression matches the test expression;

Procedure execution continues with the first statement after the end of the Select Case statement (after “End Select”).

Excel VBA Select Case Between 2 Values Example Macro

I create the Excel VBA Select Case between 2 values example macro as follows:

(1) Declare a Sub procedure (SelectCaseBetween2Values).

Sub SelectCaseBetween2Values()
    'Source: https://powerspreadsheets.com/
    'More information: https://powerspreadsheets.com/excel-vba-select-case/
    
    '...

End Sub

(2) Declare a variable (MyQuarterMessage) of the String data type.

Sub SelectCaseBetween2Values()
    'Source: https://powerspreadsheets.com/
    'More information: https://powerspreadsheets.com/excel-vba-select-case/
    
    'Declare variable to represent message to be displayed in message box
    Dim MyQuarterMessage As String
    
    '...

End Sub

(3) Enter the opening and closing statements of the Select Case statement.

Sub SelectCaseBetween2Values()
    'Source: https://powerspreadsheets.com/
    'More information: https://powerspreadsheets.com/excel-vba-select-case/
    
    'Declare variable to represent message to be displayed in message box
    Dim MyQuarterMessage As String
    
    Select Case '...
        '...
    End Select
    
    '...

End Sub

(4) Specify the numeric test expression VBA uses to identify the set of statements to execute, as a numeric expression.

The test expression I use (Month(Date:=Date)) returns an integer (1 to 12) representing the current month of the year.

Sub SelectCaseBetween2Values()
    'Source: https://powerspreadsheets.com/
    'More information: https://powerspreadsheets.com/excel-vba-select-case/
    
    'Declare variable to represent message to be displayed in message box
    Dim MyQuarterMessage As String
    
    Select Case Month(Date:=Date)
        '...
    End Select
    
    '...

End Sub

(5) Specify the numeric case expressions used by VBA to identify the set of statements to execute. I use the To keyword to specify a range of values for each single case expression.

The test expression I specified in step #4 returns an integer between 1 and 12.

The case expressions I specify (considering the above) are:

1 To 3.
4 To 6.
7 To 9.
10 To 12.

Sub SelectCaseBetween2Values()
    'Source: https://powerspreadsheets.com/
    'More information: https://powerspreadsheets.com/excel-vba-select-case/
    
    'Declare variable to represent message to be displayed in message box
    Dim MyQuarterMessage As String
    
    Select Case Month(Date:=Date)
        Case 1 To 3: '...
        Case 4 To 6: '...
        Case 7 To 9: '...
        Case 10 To 12: '...
    End Select
    
    '...

End Sub

(6) Specify the statement to be executed when the value returned by the numeric test expression (I specified in step #4) is between a case expression's 2 values (I specified in step #5).

The statements I use assign a string to the MyQuarterMessage variable (I declared in step #2). The assigned string varies, depending on the applicable numeric case expression (I specified in step #5):

1 To 3: Assigned string is “It's Q1”.
4 To 6: Assigned string is “It's Q2”.
7 To 9: Assigned string is “It's Q3”.
10 To 12: Assigned string is “It's Q4”.

Sub SelectCaseBetween2Values()
    'Source: https://powerspreadsheets.com/
    'More information: https://powerspreadsheets.com/excel-vba-select-case/
    
    'Declare variable to represent message to be displayed in message box
    Dim MyQuarterMessage As String
    
    'Assign a string to the MyQuarterMessage variable, depending on the current month
    Select Case Month(Date:=Date)
        Case 1 To 3: MyQuarterMessage = "It's Q1"
        Case 4 To 6: MyQuarterMessage = "It's Q2"
        Case 7 To 9: MyQuarterMessage = "It's Q3"
        Case 10 To 12: MyQuarterMessage = "It's Q4"
    End Select
    
    '...

End Sub

(7) Display a message box (MsgBox) with the value held by the MyQuarterMessage variable.

Sub SelectCaseBetween2Values()
    'Source: https://powerspreadsheets.com/
    'More information: https://powerspreadsheets.com/excel-vba-select-case/
    
    'Declare variable to represent message to be displayed in message box
    Dim MyQuarterMessage As String
    
    'Assign a string to the MyQuarterMessage variable, depending on the current month
    Select Case Month(Date:=Date)
        Case 1 To 3: MyQuarterMessage = "It's Q1"
        Case 4 To 6: MyQuarterMessage = "It's Q2"
        Case 7 To 9: MyQuarterMessage = "It's Q3"
        Case 10 To 12: MyQuarterMessage = "It's Q4"
    End Select
    
    'Display message box with string held by the MyQuarterMessage variable
    MsgBox MyQuarterMessage

End Sub

The image below displays the message box shown by Excel when I execute the Excel VBA Select Case between 2 values example macro. I execute the example macro (and create this screenshot) during April.

Excel VBA Select Case between 2 values example macro results

Select Case Between 2 Values vs. If… Then… ElseIf… Between 2 Values Example Macro

The following If… Then… ElseIf… between 2 values example macro is an equivalent example macro (to the Select Case between 2 values example macro I explain above) working with the If… Then… Else statement (vs. the Select Case statement).

Sub SelectCaseBetween2ValuesVsIfThenElseIfBetween2Values()
    'Source: https://powerspreadsheets.com/
    'More information: https://powerspreadsheets.com/excel-vba-select-case/
    
    'Declare variables to represent:
        '(1) Number representing month
        '(2) Message to be displayed in message box
    Dim MyCurrentMonth As Integer
    Dim MyQuarterMessage As String
    
    'Assign number representing month to the MyCurrentMonth variable
    MyCurrentMonth = Month(Date:=Date)
    
    'Assign a string to the MyQuarterMessage variable, depending on the current month
    If (MyCurrentMonth >= 1) And (MyCurrentMonth <= 3) Then
        MyQuarterMessage = "It's Q1"
    ElseIf (MyCurrentMonth >= 4) And (MyCurrentMonth <= 6) Then
        MyQuarterMessage = "It's Q2"
    ElseIf (MyCurrentMonth >= 7) And (MyCurrentMonth <= 9) Then
        MyQuarterMessage = "It's Q3"
    ElseIf (MyCurrentMonth >= 10) And (MyCurrentMonth <= 12) Then
        MyQuarterMessage = "It's Q4"
    End If
    
    'Display message box with string held by the MyQuarterMessage variable
    MsgBox MyQuarterMessage

End Sub

Notice how the Select Case statement results in more efficient VBA code that's easier to:

Read;
Follow;
Understand; and
Work with.

The image below displays the message box shown by Excel when I execute the Excel VBA If… Then… ElseIf… between 2 values example macro. I execute the example macro (and create this screenshot) during April.

Excel VBA Select Case between 2 values vs. If... Then... ElseIf... between 2 values example macro results

(5) Excel VBA Select Case Multiple Values

In this Section, you learn how to create a VBA Select Case multiple values statement to conditionally execute a set of statements based on whether an expression returns one of multiple (other) values.

Excel VBA Select Case Multiple Values Snippet Template/Structure

The following is the Excel VBA Select Case multiple values snippet template/structure I explain (step-by-step) in the Sections below.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-vba-select-case/
Select Case NumericTestExpression
    Case Value1Case1, Value2Case1, ..., Value#Case1
        CaseStatements1
    Case Value1Case2, Value2Case2, ..., Value#Case2
        CaseStatements2
    '...
    Case Value1Case#, Value2Case#, ..., Value#Case#
        CaseStatements#
    Case Else
        ElseStatements
End Select

Step-by-Step Process to Set Up an Excel VBA Select Case Multiple Values Statement

Do the following to create an Excel VBA Select Case multiple values 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/excel-vba-select-case/
Select Case '...
    '...
End Select

(2) Specify the test expression VBA uses to identify the set of statements to execute, as a numeric expression (an expression that can be evaluated as a number): NumericTestExpression.

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

(3) Specify the numeric case expressions used by VBA to identify the set of statements to execute. Use commas to separate multiple values inside a single numeric case expression.

Value1Case1, Value2Case1, …, Value#Case1.
Value1Case2, Value2Case2, …, Value#Case2.
…
Value1Case#, Value2Case#, …, Value#Case#.

You can (as a general rule) include as many case expressions as required inside a single Select Case statement.

Each case expression is preceded by the Case keyword:

Case Value1Case1, Value2Case1, …, Value#Case1.
Case Value1Case2, Value2Case2, …, Value#Case2.
…
Case Value1Case#, Value2Case#, …, Value#Case#.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-vba-select-case/
Select Case NumericTestExpression
    Case Value1Case1, Value2Case1, ..., Value#Case1
        '...
    Case Value1Case2, Value2Case2, ..., Value#Case2
        '...
    '...
    Case Value1Case#, Value2Case#, ..., Value#Case#
        '...
    '...
End Select

The Case keyword plus associated case expression (Case Value1Case#, Value2Case#, …, Value#Case#) form a Case clause.

When the value returned by the numeric test expression (you specified in step #2) is equal to one of the multiple values inside a case expression, the Select Case statement:

Executes the set of statements (you specify in step #4) associated to the applicable Case clause (whose case expression matched the test expression); and
Exits the Select Case statement.

(4) Specify the set of statements to be executed when the value returned by the numeric test expression (you specified in step #2) is equal to one of the multiple values (you specified in step #3) inside a case expression.

CaseStatements1.
CaseStatements2.
…
CaseStatements#.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-vba-select-case/
Select Case NumericTestExpression
    Case Value1Case1, Value2Case1, ..., Value#Case1
        CaseStatements1
    Case Value1Case2, Value2Case2, ..., Value#Case2
        CaseStatements2
    '...
    Case Value1Case#, Value2Case#, ..., Value#Case#
        CaseStatements#
    '...
End Select

(5) Specify the set of statements to be executed if the value returned by the numeric test expression (you specified in step #2) isn’t equal to any of the multiple values inside any of the case expressions (you specified in step #3). These catch-all statements follow the Case Else keyword.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-vba-select-case/
Select Case NumericTestExpression
    Case Value1Case1, Value2Case1, ..., Value#Case1
        CaseStatements1
    Case Value1Case2, Value2Case2, ..., Value#Case2
        CaseStatements2
    '...
    Case Value1Case#, Value2Case#, ..., Value#Case#
        CaseStatements#
    Case Else
        ElseStatements
End Select

Additional Cues for Excel VBA Select Case Multiple Values

(2) Consider indenting the statements inside Select Case statements.

(3) Consider working with the separator character (:) to separate:

The Case clause (Case Value1Case#, Value2Case#, …, Value#Case#) and the statements to be executed (CaseStatements#); or
The Case Else keyword (Case Else) and the catch-all statements (ElseStatements);

When the following 2 conditions are met:

The case results in a single statement (CaseStatement#) being executed or there's (only) a single catch-all statement (ElseStatement); and
The resulting line of VBA code is not excessively long.

(3) You have a significant degree of flexibility when specifying case expressions inside a Select Case statement. This flexibility includes the possibility to specify multiple values for a single case expression (as you learned in this Section).

(4) As a general rule: Organize case expressions:

By decreasing order of probability.
In such a way that any case expression excludes subsequent case expressions.

(5) The Case Else keyword/clause (Case Else: ElseStatements) is optional.

(6) If:

You omit the Case Else clause; and
No case expression matches the test expression;

Procedure execution continues with the first statement after the end of the Select Case statement (after “End Select”).

Excel VBA Select Case Multiple Values Example Macro

I create the VBA Select Case multiple values example macro as follows:

(1) Declare a Sub procedure (SelectCaseMultipleValues).

Sub SelectCaseMultipleValues()
    'Source: https://powerspreadsheets.com/
    'More information: https://powerspreadsheets.com/excel-vba-select-case/
    
    '...

End Sub

(2) Declare a variable (MyMonthMessage) of the String data type.

Sub SelectCaseMultipleValues()
    'Source: https://powerspreadsheets.com/
    'More information: https://powerspreadsheets.com/excel-vba-select-case/
    
    'Declare variable to represent message to be displayed in message box
    Dim MyMonthMessage As String
    
    '...

End Sub

(3) Enter the opening and closing statements of the Select Case statement.

Sub SelectCaseMultipleValues()
    'Source: https://powerspreadsheets.com/
    'More information: https://powerspreadsheets.com/excel-vba-select-case/
    
    'Declare variable to represent message to be displayed in message box
    Dim MyMonthMessage As String
    
    Select Case '...
        '...
    End Select
    
    '...

End Sub

(4) Specify the numeric test expression VBA uses to identify the set of statements to execute, as a numeric expression.

The test expression I use (Month(Date:=Date)) returns an integer (1 to 12) representing the current month of the year.

Sub SelectCaseMultipleValues()
    'Source: https://powerspreadsheets.com/
    'More information: https://powerspreadsheets.com/excel-vba-select-case/
    
    'Declare variable to represent message to be displayed in message box
    Dim MyMonthMessage As String
    
    Select Case Month(Date:=Date)
        '...
    End Select
    
    '...

End Sub

(5) Specify the numeric case expressions used by VBA to identify the set of statements to execute. I use commas to separate multiple values inside a single numeric case expression.

The test expression I specified in step #4 returns an integer between 1 and 12.

The case expressions I specify (considering the above) are:

1, 4, 7, 10.
2, 5, 8, 11.
3, 6, 9, 12.

Sub SelectCaseMultipleValues()
    'Source: https://powerspreadsheets.com/
    'More information: https://powerspreadsheets.com/excel-vba-select-case/
    
    'Declare variable to represent message to be displayed in message box
    Dim MyMonthMessage As String
    
    Select Case Month(Date:=Date)
        Case 1, 4, 7, 10: '...
        Case 2, 5, 8, 11: '...
        Case 3, 6, 9, 12: '...
    End Select
    
    '...

End Sub

(6) Specify the statement to be executed when the value returned by the numeric test expression (I specified in step #4) is equal to one of the multiple values (I specified in step #5) inside a case expression.

The statements I use assign a string to the MyMonthMessage variable (I declared in step #2). The assigned string varies, depending on the applicable numeric case expression (I specified in step #5):

1, 4, 7, 10: Assigned string is “It's January, April, July, or October”.
2, 5, 8, 11: Assigned string is “It's February, May, August, or November”.
3, 6, 9, 12: Assigned string is “It's March, June, September, or December”.

Sub SelectCaseMultipleValues()
    'Source: https://powerspreadsheets.com/
    'More information: https://powerspreadsheets.com/excel-vba-select-case/
    
    'Declare variable to represent message to be displayed in message box
    Dim MyMonthMessage As String
    
    'Assign a string to the MyMonthMessage variable, depending on the current month
    Select Case Month(Date:=Date)
        Case 1, 4, 7, 10: MyMonthMessage = "It's January, April, July, or October"
        Case 2, 5, 8, 11: MyMonthMessage = "It's February, May, August, or November"
        Case 3, 6, 9, 12: MyMonthMessage = "It's March, June, September, or December"
    End Select
    
    '...

End Sub

(7) Display a message box (MsgBox) with the value held by the MyMonthMessage variable.

Sub SelectCaseMultipleValues()
    'Source: https://powerspreadsheets.com/
    'More information: https://powerspreadsheets.com/excel-vba-select-case/
    
    'Declare variable to represent message to be displayed in message box
    Dim MyMonthMessage As String
    
    'Assign a string to the MyMonthMessage variable, depending on the current month
    Select Case Month(Date:=Date)
        Case 1, 4, 7, 10: MyMonthMessage = "It's January, April, July, or October"
        Case 2, 5, 8, 11: MyMonthMessage = "It's February, May, August, or November"
        Case 3, 6, 9, 12: MyMonthMessage = "It's March, June, September, or December"
    End Select
    
    'Display message box with string held by the MyMonthMessage variable
    MsgBox MyMonthMessage

End Sub

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

Excel VBA Select Case multiple values example macro results

Select Case Multiple Values vs. If… Then… ElseIf… Multiple Values Example Macro

The following If… Then… ElseIf… multiple values example macro is an equivalent example macro (to the Select Case multiple values example macro I explain above) working with the If… Then… Else statement (vs. the Select Case statement).

Sub SelectCaseMultipleValuesVsIfThenElseIfMultipleValues()
    'Source: https://powerspreadsheets.com/
    'More information: https://powerspreadsheets.com/excel-vba-select-case/
    
    'Declare variables to represent:
        '(1) Number representing month
        '(2) Message to be displayed in message box
    Dim MyCurrentMonth As Integer
    Dim MyMonthMessage As String
    
    'Assign number representing month to the MyCurrentMonth variable
    MyCurrentMonth = Month(Date:=Date)
    
    'Assign a string to the MyMonthMessage variable, depending on the current month
    If (MyCurrentMonth = 1) Or (MyCurrentMonth = 4) Or (MyCurrentMonth = 7) Or (MyCurrentMonth = 10) Then
        MyMonthMessage = "It's January, April, July, or October"
    ElseIf (MyCurrentMonth = 2) Or (MyCurrentMonth = 5) Or (MyCurrentMonth = 8) Or (MyCurrentMonth = 11) Then
        MyMonthMessage = "It's February, May, August, or November"
    ElseIf (MyCurrentMonth = 3) Or (MyCurrentMonth = 6) Or (MyCurrentMonth = 9) Or (MyCurrentMonth = 12) Then
        MyMonthMessage = "It's March, June, September, or December"
    End If
    
    'Display message box with string held by the MyMonthMessage variable
    MsgBox MyMonthMessage

End Sub

Notice how the Select Case statement results in more efficient VBA code that's easier to:

Read;
Follow;
Understand; and
Work with.

The image below displays the message box shown by Excel when I execute the Excel VBA If… Then… ElseIf… multiple values example macro. I execute the example macro (and create this screenshot) during May.

Excel VBA Select Case vs. If... Then... Else multiple values example macro results

(6) Excel VBA Select Case Multiple Conditions

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

An expression's value; and
Multiple conditions (grouping several cases inside a single case expression).

Excel VBA Select Case Multiple Conditions Snippet Template/Structure

The following is the Excel VBA Select Case multiple conditions snippet template/structure I explain (step-by-step) in the Sections below.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-vba-select-case/
Select Case TestExpression
    Case CaseExpression1
        CaseStatements1
    Case CaseExpression2
        CaseStatements2
    '...
    Case CaseExpression#
        CaseStatements#
    Case Else
        ElseStatements
End Select

When creating a Select Case multiple conditions statement, each CaseExpression# can follow (as appropriate) one of the following templates/structures:

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-vba-select-case/

LowerBoundCase To UpperBoundCase

Is ComparisonOperator ComparisonCase

Case1, Case2, Case3, …, Case#

CaseRange1, CaseRange2, CaseRange3, …, CaseRange#

Step-by-Step Process to Set Up an Excel VBA Select Case Multiple Conditions Statement

Do the following to create an Excel VBA Select Case multiple conditions 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/excel-vba-select-case/
Select Case '...
    '...
End Select

(2) Specify the test expression VBA uses to identify the set of statements to execute: TestExpression.

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

(3) Specify the case expressions used by VBA to identify the set of statements to execute:

CaseExpression1.
CaseExpression2.
…
CaseExpression#.

You can (as a general rule) include as many case expressions as required inside a single Select Case statement.

Each case expression is preceded by the Case keyword:

Case CaseExpression1.
Case CaseExpression2.
…
Case CaseExpression#.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-vba-select-case/
Select Case TestExpression
    Case CaseExpression1
        '...
    Case CaseExpression2
        '...
    '...
    Case CaseExpression#
        '...
End Select

When creating a Select Case multiple conditions statement, use the following constructs and structures to group several cases inside a single case expression:

(3.1.) Use the To and Is keywords to specify a range of values or strings for a single case expression.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-vba-select-case/

'To keyword
LowerBoundCase To UpperBoundCase

'Is keyword
Is ComparisonOperator ComparisonCase

(3.2.) Use commas to separate multiple:

Values or strings; or
Value or string ranges (usually created by working with the To or Is keywords I explain above);

Inside a single case expression.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-vba-select-case/

'Values or strings
Case1, Case2, Case3, …, Case#

'Value or string ranges
CaseRange1, CaseRange2, CaseRange3, …, CaseRange#

The Case keyword plus associated case expression (Case CaseExpression#) form a Case clause.

When the test expression (you specified in step #2) matches an individual case expression, the Select Case statement:

Executes the set of statements (you specify in step #4) associated to the applicable Case clause (whose case expression matched the test expression); and
Exits the Select Case statement.

(4) Specify the set of statements to be executed when the applicable case expression (you specified in step #3) matches the test expression (you specified in step #2).

CaseStatements1.
CaseStatements2.
…
CaseStatements#.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-vba-select-case/
Select Case TestExpression
    Case CaseExpression1
        CaseStatements1
    Case CaseExpression2
        CaseStatements2
    '...
    Case CaseExpression#
        CaseStatements#
    '...
End Select

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-vba-select-case/
Select Case TestExpression
    Case CaseExpression1
        CaseStatements1
    Case CaseExpression2
        CaseStatements2
    '...
    Case CaseExpression#
        CaseStatements#
    Case Else
        ElseStatements
End Select

Additional Cues for Excel VBA Select Case Multiple Conditions

(2) Consider indenting the statements inside Select Case statements.

(3) Consider working with the separator character (:) to separate:

The Case clause (Case CaseExpression#) and the statements to be executed (CaseStatements#); or
The Case Else keyword (Case Else) and the catch-all statements (ElseStatements);

When the following 2 conditions are met:

The case results in a single statement (CaseStatement#) being executed or there's (only) a single catch-all statement (ElseStatement); and
The resulting line of VBA code is not excessively long.

(3) You have a significant degree of flexibility when specifying case expressions inside a Select Case statement. This flexibility includes the possibility to group several cases inside a single case expression (as you learned in this Section).

(4) When using the Is keyword to specify a range of values or strings for a single case expression (Is ComparisonOperator ComparisonCase), ComparisonOperator is a comparison operator, excluding the following operators:

Is.
Like.

(5) As a general rule: Organize case expressions:

By decreasing order of probability.
In such a way that any case expression excludes subsequent case expressions.

(6) The Case Else keyword/clause (Case Else: ElseStatements) is optional.

(7) If:

You omit the Case Else clause; and
No case expression matches the test expression;

Procedure execution continues with the first statement after the end of the Select Case statement (after “End Select”).

Excel VBA Select Case Multiple Conditions Example Macro

I create the Excel VBA Select Case multiple conditions example macro as follows:

(1) Declare a Sub procedure (SelectCaseMultipleConditions).

Sub SelectCaseMultipleConditions()
    'Source: https://powerspreadsheets.com/
    'More information: https://powerspreadsheets.com/excel-vba-select-case/
    
    '...

End Sub

(2) Declare a variable (MyMonthMessage) of the String data type.

Sub SelectCaseMultipleConditions()
    'Source: https://powerspreadsheets.com/
    'More information: https://powerspreadsheets.com/excel-vba-select-case/
    
    'Declare variable to represent message to be displayed in message box
    Dim MyMonthMessage As String
    
    '...

End Sub

(3) Enter the opening and closing statements of the Select Case statement.

Sub SelectCaseMultipleConditions()
    'Source: https://powerspreadsheets.com/
    'More information: https://powerspreadsheets.com/excel-vba-select-case/
    
    'Declare variable to represent message to be displayed in message box
    Dim MyMonthMessage As String
    
    Select Case '...
        
        '...
    
    End Select
    
    '...

End Sub

(4) Specify the test expression VBA uses to identify the set of statements to execute.

The test expression I use (Month(Date:=Date)) returns an integer (1 to 12) representing the current month of the year.

Sub SelectCaseMultipleConditions()
    'Source: https://powerspreadsheets.com/
    'More information: https://powerspreadsheets.com/excel-vba-select-case/
    
    'Declare variable to represent message to be displayed in message box
    Dim MyMonthMessage As String
    
    Select Case Month(Date:=Date)
        
        '...
    
    End Select
    
    '...

End Sub

(5) Specify the case expressions used by VBA to identify the set of statements to execute.

I use the following constructs and structures to group several cases inside a single case expression:

The To and Is keywords to specify a range of values or strings for a single case expression.
Commas to separate multiple values or strings.

The test expression I specified in step #4 returns an integer between 1 and 12.

The case expressions I specify (considering the above) are:

Is < 7.
7 To 9.
10, 11.

Sub SelectCaseMultipleConditions()
    'Source: https://powerspreadsheets.com/
    'More information: https://powerspreadsheets.com/excel-vba-select-case/
    
    'Declare variable to represent message to be displayed in message box
    Dim MyMonthMessage As String
    
    Select Case Month(Date:=Date)
        
        Case Is < 7: '...
        
        Case 7 To 9: '...
        
        Case 10, 11: '...
        
        Case Else: '...
    
    End Select
    
    '...

End Sub

(6) Specify the statement to be executed when the applicable case expression (I specified in step #5) matches the test expression (I specified in step #4).

The statements I use assign a string to the MyMonthMessage variable (I declared in step #2). The assigned string varies, depending on the applicable case expression (I specified in step #5):

Is < 7: Assigned string is “It's H1”.
7 To 9: Assigned string is “It's Q3”.
10, 11: Assigned string is “It's Q4, but not yet December”.

Sub SelectCaseMultipleConditions()
    'Source: https://powerspreadsheets.com/
    'More information: https://powerspreadsheets.com/excel-vba-select-case/
    
    'Declare variable to represent message to be displayed in message box
    Dim MyMonthMessage As String
    
    'Assign a string to the MyMonthMessage variable, depending on the current month
    Select Case Month(Date:=Date)
        
        'If the current month is less than 7, message is "It's H1"
        Case Is < 7: MyMonthMessage = "It's H1"
        
        'If the current month is from 7 to 9, message is "It's Q3"
        Case 7 To 9: MyMonthMessage = "It's Q3"
        
        'If the current month is 10 or 11, message is "It's Q4, but not yet December"
        Case 10, 11: MyMonthMessage = "It's Q4, but not yet December"
        
        '...
    
    End Select
    
    '...

End Sub

(7) Specify the catch-all statement to be executed if no case expression (from the case expressions I specified in step #5) matches the test expression (I specified in step #4).

The statement I use assigns a string to the MyMonthMessage variable (I declared in step #2): “It's December”.

Sub SelectCaseMultipleConditions()
    'Source: https://powerspreadsheets.com/
    'More information: https://powerspreadsheets.com/excel-vba-select-case/
    
    'Declare variable to represent message to be displayed in message box
    Dim MyMonthMessage As String
    
    'Assign a string to the MyMonthMessage variable, depending on the current month
    Select Case Month(Date:=Date)
        
        'If the current month is less than 7, message is "It's H1"
        Case Is < 7: MyMonthMessage = "It's H1"
        
        'If the current month is from 7 to 9, message is "It's Q3"
        Case 7 To 9: MyMonthMessage = "It's Q3"
        
        'If the current month is 10 or 11, message is "It's Q4, but not yet December"
        Case 10, 11: MyMonthMessage = "It's Q4, but not yet December"
        
        'If the current month doesn't match any of the previous case expressions, message is "It's December"
        Case Else: MyMonthMessage = "It's December"
    
    End Select
    
    '...

End Sub

(8) Display a message box (MsgBox) with the value held by the MyMonthMessage variable.

Sub SelectCaseMultipleConditions()
    'Source: https://powerspreadsheets.com/
    'More information: https://powerspreadsheets.com/excel-vba-select-case/
    
    'Declare variable to represent message to be displayed in message box
    Dim MyMonthMessage As String
    
    'Assign a string to the MyMonthMessage variable, depending on the current month
    Select Case Month(Date:=Date)
        
        'If the current month is less than 7, message is "It's H1"
        Case Is < 7: MyMonthMessage = "It's H1"
        
        'If the current month is from 7 to 9, message is "It's Q3"
        Case 7 To 9: MyMonthMessage = "It's Q3"
        
        'If the current month is 10 or 11, message is "It's Q4, but not yet December"
        Case 10, 11: MyMonthMessage = "It's Q4, but not yet December"
        
        'If the current month doesn't match any of the previous case expressions, message is "It's December"
        Case Else: MyMonthMessage = "It's December"
    
    End Select
    
    'Display message box with string held by the MyMonthMessage variable
    MsgBox MyMonthMessage

End Sub

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

Excel VBA Select Case multiple conditions example macro results

Select Case Multiple Conditions vs. If… Then… ElseIf… Else Multiple Conditions Example Macro

The following If… Then… ElseIf… Else multiple conditions example macro is an equivalent example macro (to the Select Case multiple conditions example macro I explain above) working with the If… Then… Else statement (vs. the Select Case statement).

Sub SelectCaseMultipleConditionsVsIfThenElseIfElseMultipleConditions()
    'Source: https://powerspreadsheets.com/
    'More information: https://powerspreadsheets.com/excel-vba-select-case/
    
    'Declare variables to represent:
        '(1) Number representing month
        '(2) Message to be displayed in message box
    Dim MyCurrentMonth As Integer
    Dim MyMonthMessage As String
    
    'Assign number representing month to the MyCurrentMonth variable
    MyCurrentMonth = Month(Date:=Date)
    
    'Assign a string to the MyMonthMessage variable, depending on the current month
    If MyCurrentMonth < 7 Then
        MyMonthMessage = "It's H1"
    ElseIf (MyCurrentMonth >= 7) And (MyCurrentMonth <= 9) Then
        MyMonthMessage = "It's Q3"
    ElseIf (MyCurrentMonth = 10) Or (MyCurrentMonth = 11) Then
        MyMonthMessage = "It's Q4, but not yet December"
    Else
        MyMonthMessage = "It's December"
    End If
    
    'Display message box with string held by the MyMonthMessage variable
    MsgBox MyMonthMessage

End Sub

Notice how the Select Case statement results in more efficient VBA code that's easier to:

Read;
Follow;
Understand; and
Work with.

The image below displays the message box shown by Excel when I execute the Excel VBA If… Then… ElseIf… Else multiple conditions example macro. I execute the example macro (and create this screenshot) during March.

Excel VBA Select Case vs. If... Then... Else multiple conditions example macro results

Download the Excel VBA Select Case Example Workbook

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

The following Excel Macro and VBA Tutorials may help you better understand and implement the contents above.

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:
- Excel VBA Change Font Color Based on Cell Value: Click here to open.
- Create a message box: Click here to open.
- Create an input box: Click here to open.

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

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.