Excel Visual Basic for Applications online training

This page has 0 threads | Add post

Finding our tutorials useful? Please consider helping us to share more!

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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 the highlighted code shown here.

We can replace the logical test with the value True:

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

Test that this works by altering the weight and height:

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

Add a label to cell A6 in the worksheet:

Add a label which resembles this one.

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

Range("B6").Value = Switch(

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

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

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

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"

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")

Test that your code works:

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

Save and close the workbook.

This page has 0 threads

Add a new post

MODULES

LESSONS

TOPICS

Files Needed

Completed Code

Creating a Basic Switch Expression

Adding More Conditions

Creating an Else Clause

Head office

London

Manchester