Module 3 - Conditions and Loops
Lesson 3.3 - Select Case Statements
Topic 3.3.1 - The Select Case Statement

So far in this module you've used the If statement to test a variety of conditions and perform different sets of actions depending on the result.  VBA has another conditional statement called Select Case.  While the If statement is useful when you want to test a variety of conditions on multiple values, the Select Case statement is more suited to testing a single value, as this part of the lesson shows you!

Files Needed

You can click here to download the file for this page.

Completed Code

You can click here to download a file containing the sample code.

The Example Workbook

Extract and open the workbook linked to in the Files Needed section above.  You'll find a version of a workbook that you've seen several times already during this module:

Example

As usual, you can enter details in column B then click the button to copy the data to the table which starts in column D.

You can see the code attached to the button in the VBE:

Code

The code copies and pastes the details that have been entered.

In this example, we'd like to create a description for the film's rating based on its score and add this to column G in the table.

Creating a Select Case Statement

For the first example, we'll create a Select Case statement which tests if the score is equal to 0.  Begin by adding a new line below the existing code in the Add_To_List subroutine:

Select Case

A Select Case statement begins by stating which value you want to test. Each condition that you add to the statement will be compared to this single value.  Here, we're assigning the value of cell B4 to the statement.

 

On the next line, you can write the first condition you want to test.  Each condition you add begins with the word Case:

Case

Testing a single value is as simple as writing Case followed by the value.

 

Below this line you can write the set of instructions you want to execute if the condition is met:

Action

In this example, we'll simply write a description into column G of the table.

 

To finish, you must add the End Select statement:

End Select

You can add as many conditions within the Select Case - End Select block as you like.

 

Enter some details into column B and assign a score of 0:

Test

Enter any title and date, along with a score of 0.

 

Click the button and check that the result appears as expected:

Result

Your description appears in column G.

 

Testing for Multiple Values

For the next example, we'll test if the film has scored 1, 2 or 3 and assign a rating of Bad if the condition is met.  Start by adding a new line within the Select Case - End Select block:

Case list

To test a list of specific values, add the word Case followed by the comma-separated list of values you want to test for.

 

Add the instruction you want to execute if the condition is met:

Action

Again, we'll add a single instruction which writes the rating into column G.

 

Test the condition works by adding a new film with a rating of 1, 2 or 3:

Result

Your rating will appear in column G when you click the button.

You could, of course, achieve the same using an If statement, but you'd need to use multiple conditions joined with the Or operator to do so.

Testing a Range of Values

The Select Case statement makes it easy to test a range of values.  For the next example we'll check if the score is between 4 and 6 and describe the film as Average if so.  Add a new line to the Select Case - End Select block:

test range

You can test if a value falls within a range as shown here.

 

You can then add the instructions you'd like to perform:

Instructions

Assign the description to the cell as before.

 

Add another case to describe films scoring between 7 and 8 as Good:

Second range

You could also write the condition as Case 7, 8 if you prefer.

 

Test that your conditions work by adding a film with any score between 4 and 8:

Test

Feel free to test more than one value!

 

Overlapping Ranges

If it was possible for the film score to be a decimal value, you may want to overlap the ranges you are testing for in the Select Case statement:

Decimal

Currently, any value which falls between 6 and 7 won't receive a rating.

 

You can edit the last condition you added as shown below:

Overlap

Overlap the conditions so that the ending value of one condition is the same as the starting value of the next.

 

Now a value of 6.5 will be categorised as Good, while a value of exactly 6 will be Average:

Decimal

Now a score of 6.5 will receive a rating.

 

Conditions in a Select Case statement are evaluated in the order they appear in your code.  When a value meets the criteria for one of the Case expressions, it won't be tested against any remaining conditions below it.

Using Comparison Operators

You can use comparison operators (such as greater than, less than, etc.) in your Select Case statements, although the syntax is a little unusual.  Let's demonstrate this by describing films with a score of more than 8 as Great:

Greater than

You must include the word Is when using a comparison operator, as shown here.

 

If you forget to add the Is keyword, VBA's syntax checker will insert it automatically.

Add an instruction below the condition as shown below:

INstruction

Add the description to column G in the table.

 

Test that you see the correct result when you add a film with a score higher than 8:

Result

Check that your rating appears as expected.

 

Using the Else Clause

The Select Case statement also has an Else clause.  We could use the Else clause to replace the condition we added in the previous example:

remove condition

Remove this part of the condition.

 

Edit the code so that it looks like this:

Case Else

The Case Else section will handle any values which don't meet any of the previous criteria.

 

Test that the Else clause successfully describes films with a score higher than 8:

Result

Any score higher than 8 will result in a Great rating.

 

Beware than any score which doesn't meet the specific criteria you have added to the Select Case statement will create a rating of Great.  This includes strings and negative numbers.  You could use If statements to validate the score before your code reaches the Select Case statement.

To practise using the Select Case statement:

  1. In the same workbook, add the title Season to column H:
Season

We'd like to record the season in which we watched the film.

 
  1. We can calculate the season from the number of the month in which we watched a film:
Season Month numbers
Spring 3, 4, 5
Summer 6, 7, 8
Autumn 9, 10, 11
Winter 12, 1, 2
  1. Begin a new Select Case statement in the same subroutine which uses the Month function to calculate the number of the month in which a film was watched:

Select Case Month(Range("B3").Value)

 

End Select

  1. Test if the month is 1, 2 or 12:

Select Case Month(Range("B3").Value)

Case 1, 2, 12

 

End Select

  1. Add an instruction to write the word Winter into column H:

Select Case Month(Range("B3").Value)

Case 1, 2, 12

ActiveCell.Offset(0, 4).Value = "Winter"

End Select

  1. Add a new condition to test if the month is between 3 and 5 and write the word Spring if it is:

Select Case Month(Range("B3").Value)

Case 1, 2, 12

ActiveCell.Offset(0, 4).Value = "Winter"

Case 3 To 5

ActiveCell.Offset(0, 4).Value = "Spring"

End Select

  1. Add two more conditions to create the correct descriptions for Summer and Autumn:

Select Case Month(Range("B3").Value)

Case 1, 2, 12

ActiveCell.Offset(0, 4).Value = "Winter"

Case 3 To 5

ActiveCell.Offset(0, 4).Value = "Spring"

Case 6 To 8

ActiveCell.Offset(0, 4).Value = "Summer"

Case 9 To 11

ActiveCell.Offset(0, 4).Value = "Autumn"

End Select

  1. Add some details to the worksheet and test that you see the correct season name appear in column H:
Test

Feel free to test as many different dates as you like. Beware that if you enter a value in cell B3 which isn't a date, you'll encounter a run-time error.

 
  1. Add an If statement to the top of the subroutine which will exit from the procedure if the value of B3 is not a date:

If Not IsDate(Range("B3").Value) Then

Exit Sub

End If

  1. Save and close the workbook.
This page has 0 threads Add post