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.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:

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:

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:

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:

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:

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:

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:

The completed expression.

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

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:

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: