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:
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:
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:
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:
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:
In this example, we'll simply write a description into column
G of the table.
To finish, you must add the End Select statement:
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:
Enter any title and date, along with a score of
0.
Click the button and check that the result appears as expected:
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:
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:
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:
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:
You can test if a value falls within a range as shown here.
You can then add the instructions you'd like to perform:
Assign the description to the cell as before.
Add another case to describe films scoring between 7 and
8 as Good:
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:
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:
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 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:
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:
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:
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:
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 this part of the condition.
Edit the code so that it looks like this:
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:
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.
