Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
546 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
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.
You can click here to download the file for this page.
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.
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.
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 the last line of the code so that it resembles this.
You can now begin replacing the previous code with a new IIf function:
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:
Create a logical test as shown here.
If the condition is met, we want to return the value of cell C2:
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:
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:
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:
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:
Range("B4").Value = IIf(
Range("B4").Value = IIf( _
Range("C3").Value = 0
Range("B4").Value = IIf( _
Range("C3").Value = 0, _
"Clean Sheet"
Range("B4").Value = IIf( _
Range("C3").Value = 0, _
"Clean Sheet", _
"")
Range("C4").Value = IIf( _
Range("B3").Value = 0, _
"Clean Sheet", _
"")
Test that each team receives the correct label when their opponent scores no goals.
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.