Module 3 - Conditions and Loops
Lesson 3.4 - Conditional Functions
Topic 3.4.2 - The Switch Function

The Switch function is a little bit like a compact Select Case statement.  You'll often find Switch useful when you have a series of mutually exclusive conditions you want to test against a single value.

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.

Extract and open the workbook linked to in the Files Needed section above.  You'll find a simple Body Mass Index (BMI) calculator worksheet:

BMI

You can enter your height in metres and weight in kilograms then click the button to see your BMI in cell B4.

 

You can see the code which runs when you click the button in the VBE:

Code

This code performs a basic calculation using the values in cells B2 and B3 and writes the answer to cell B4.

We'd like to add code to work out which BMI category the user belongs to according the to the table below:

BMI Category
< 18.5 Underweight
18.5 - 25 Healthy
25 - 30 Overweight
30 - 40 Obese
>= 40 Morbidly obese

Creating a Basic Switch Expression

We'll begin by using the Switch function to create a description for the Underweight category.  Add an instruction at the end of the WhatsMyBMI subroutine to change the value of cell B5:

Switch

The tooltip for the Switch function isn't particularly helpful.

To create a valid switch expression, you need to create at least one logical test, followed by an instruction to be performed if the logical test returns True.  We'll test if the value of cell B4 is less than 18.5 and return the word Underweight if so:

test and response

You can create as many pairs of arguments as you like. We'll stick with one logical test and one answer for now.

 

You can test the code by clicking the button on the worksheet:

Click

If none of the conditions you have written are met, the Switch function returns Null and so cell B5 remains empty.

 

Try increasing the height or reducing the weight to return a lower BMI:

Result

If Robert Wadlow had weighed the same as a certain Wise Owl, he would most certainly be classed as underweight.

 

Adding More Conditions

As mentioned earlier, you can have as many pairs of arguments in a Switch function as you like.  Extend the expression to create a description for the Healthy category:

Two conditions

You may find it easier to use continuation characters to place each pair of arguments on a different line.

 

Continue adding conditions to deal with the Overweight and Obese categories:

Four conditions

The conditions in the Switch function are evaluated in the order they appear. The result will be the argument immediately after the first condition which returns True.

 

You can test the code works by altering the weight and height to generate different results:

Different results

Try different combinations of weight and height to make sure you generate the correct results.

 

Creating an Else Clause

Unlike the Select Case statement, the Switch function doesn't have an official Else clause.  You can simulate one however, as we'll demonstrate by adding a condition for anyone whose BMI is 40 or higher.  Start by adding a final condition and answer to the expression:

Explicit test

Here we're explicitly testing if the value is greater than or equal to 40.

 

If the value reaches this final logical test then it must be greater than or equal to 40 so there's no need to explicitly test that this is true:

Replace logical test

Replace the highlighted code shown here.

 

We can replace the logical test with the value True:

True

The final logical test must return True, so why test for it?

 

Test that this works by altering the weight and height:

Result

Jon Minnoch would definitely fall into the final category!

 

To practise using the Switch function, we'll add code to determine which boxing weight division a person belongs to using the simplified table of categories shown below:

Weight Division
< 52 Flyweight
52 - 56 Bantamweight
56 - 60 Lightweight
60 - 69 Welterweight
69 - 75 Middleweight
>= 75 Heavyweight
  1. Add a label to cell A6 in the worksheet:
Division

Add a label which resembles this one.

 
  1. Using the same subroutine, add a new instruction to change the value of cell B6:

Range("B6").Value = Switch(

  1. Add the first condition and answer to create a description for the Flyweight division:

Range("B6").Value = Switch( _

Range("B3").Value < 52, "Flyweight"

  1. Continue adding conditions to create descriptions for all except the Heavyweight division:

Range("B6").Value = Switch( _

Range("B3").Value < 52, "Flyweight", _

Range("B3").Value < 56, "Bantamweight", _

Range("B3").Value < 60, "Lightweight", _

Range("B3").Value < 69, "Welterweight", _

Range("B3").Value < 75, "Middleweight"

  1. Add a final condition to create a description for the Heavyweight division:

Range("B6").Value = Switch( _

Range("B3").Value < 52, "Flyweight", _

Range("B3").Value < 56, "Bantamweight", _

Range("B3").Value < 60, "Lightweight", _

Range("B3").Value < 69, "Welterweight", _

Range("B3").Value < 75, "Middleweight", _

True, "Heavyweight")

  1. Test that your code works:
Result

The division will appear in cell B6. Try a variety of different weights to check that the code works as intended.

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