Module 3 - Conditions and Loops
Lesson 3.4 - Conditional Functions
Topic 3.4.1 - The IIf Function

At this point you've had lots of practice using the If statement to test combinations of criteria and perform a number of different actions depending on the result.  When you want to test a condition and perform a single action or return a simple value, you may find it easier to use VBA's IIf function.

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 worksheet which allows you to predict scores for the World Cup:

Predict

Clicking the button creates a predicted score in row 3 of the worksheet.

 

You can see the code that runs when the button is clicked in the VBE:

Code

Our prediction is based on nothing more than a pair of random numbers!

 

We'd like to add code to set the value of cell C7 to the name of the winning team.

Using the IIf Function

To begin, add a new instruction to the end of the Predict_Score subroutine which sets the value of cell C7:

Call IIf

Write IIf and open a set of parentheses to see a tooltip which lists the parameters of the function.

 

The first parameter of the IIf function requires a logical test.  We'll check if the value of cell B3 is higher than that of cell C3:

Logical test

You don't need to write the logical test on a separate line but it can make the code easier to read.

 

If the condition is met, we want to return the value of cell B2:

Trupart

Type a comma to move to the TruePart parameter. In our example we want to return the value of cell B2.

 

If the condition is not met, we'll assume that the other team was the winner and display the value of cell C2:

FalsePart

Type another comma to move to the FalsePart parameter, then refer to the value of cell C2.

 

Close the set of parentheses to finish the expression:

Complete

The completed expression.

 

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

Result

When the first team has a higher score than the second, their name appears in cell C7.

 

One small problem with our code is that if the scores are equal, the second team's name is shown as the winner:

Dodgy result

It's not as dodgy as some of the things FIFA has done, but we'd like to fix this particular problem.

 

Writing Nested IIf Functions

We could solve the above problem by nesting a second IIf function within the first.  Start by removing the FalsePart argument from the existing IIf function:

Remove

Remove the last line of the code so that it resembles this.

 

You can now begin replacing the previous code with a new IIf function:

IIf

Write IIf followed by an open parenthesis to begin a nested IIf function.

 

Test if the value of cell C3 is higher than that of cell B3:

New logical

Create a logical test as shown here.

 

If the condition is met, we want to return the value of cell C2:

TruePart

Type a comma followed by a reference to the value of cell C2.

 

If the previous two tests have failed, the scores must be equal.  Set the FalsePart argument to indicate this:

Draw

If the scores are equal, we'll call the game a Draw.

 

If you have two IIf functions, you'll need to close two sets of parentheses:

Parentheses

Close two sets of parentheses as shown here.

 

You can test that your code creates the correct result by clicking the button on the worksheet:

Drw

This time, the result will be shown correctly.

 

To practise using the IIf function, we'll add code to indicate whenever a team keeps a clean sheet:

  1. Using the same subroutine, add an instruction at the end to alter the value of cell B4:

Range("B4").Value = IIf(

  1. Write a logical test to check if the value of cell C3 is 0:

Range("B4").Value = IIf( _

Range("C3").Value = 0

  1. Add an instruction to return the text Clean Sheet if the condition is met:

Range("B4").Value = IIf( _

Range("C3").Value = 0, _

"Clean Sheet"

  1. Return an empty string if the condition is not met:

Range("B4").Value = IIf( _

Range("C3").Value = 0, _

"Clean Sheet", _

"")

  1. Create a separate IIf function to place the text Clean Sheet in cell C4 if the value in cell B3 is 0:

Range("C4").Value = IIf( _

Range("B3").Value = 0, _

"Clean Sheet", _

"")

  1. Click the button on the worksheet to test that the code works:
Clean sheet

Test that each team receives the correct label when their opponent scores no goals.

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