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
544 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 ...
Up to this point in the course, every procedure that you have created will execute every instruction when you run it. Sometimes, however, you will want to execute some instructions only when certain conditions have been met. This part of the lesson introduces you to the If statement which allows you to control which instructions are executed when you run a procedure.
The If statement is one of a number of statements that can affect which instructions are executed when you run a procedure. Collectively, these statements are known as Control Flow or Program Flow statements.
You can click here to download the file needed 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 workbook that you've previously encountered during this course:
You can enter details of a film in column B, then click the button to add those details to the table starting in column D.
One of the many problems with this basic system is that a user can click the button without filling in all the details:
Here the user has added an incomplete record to the table, which might cause problems when adding another item to the list.
We can add code which will only copy the data if the user has entered a value into cell B2.
You should delete any incomplete records from the table before proceeding.
Open the VBE and find the subroutine called Add_To_List:
The code should look familiar if you've completed the module on Moving Around in Excel.
We'll begin by adding a statement at the top of the subroutine to check if cell B2 is empty:
A basic If statement begins with the word If, followed by the condition you're testing, and ends with the word Then. Here we're testing if the Value of cell B2 is equal to an empty string.
The If statement evaluates the condition you've entered and returns either True or False. You can write the instruction that you want to perform if the condition is True after the Then keyword. In this simple example we'll exit from the subroutine if the condition is met:
The complete If statement in a single line of code.
Before testing the code, make sure that you've deleted the contents of cell B2:
Select the cell and press Delete to clear its contents.
In the VBE, click within the subroutine and press F8 to begin stepping through the procedure. Continue pressing F8 until you reach the line containing the If statement:
When you reach this line, you should find that only the If statement is highlighted in yellow.
When you press F8 again, the condition is evaluated and only moves to the second part of the line if it returns True:
In this case, cell B2 is empty and so the code moves to the next part of the line. You can press F8 again to end the subroutine.
Now try adding a new film title into cell B2:
Any film will do.
In the VBE, click within the subroutine and press F8 to begin stepping through the procedure. Continue until you reach the line which contains the If statement:
Press F8 until you reach this part of the subroutine.
When you press F8 again the condition will be evaluated. This time cell B2 is not empty and so the condition returns False. This means it won't execute the Exit Sub statement but will continue with the rest of the procedure:
The subroutine continues as normal. Press F5 to run it to the end.
Once the procedure has finished, you'll find the new film details in the list:
The film's details will appear at the bottom of the list.
Congratulations! You've successfully controlled the flow of a program using an If statement!
To practise using basic If statements, using the same subroutine as above:
Sub Add_To_List()
If Range("B2").Value = "" Then Exit Sub
If Range("B3").Value = "" Then Exit Sub
Sub Add_To_List()
If Range("B2").Value = "" Then Exit Sub
If Range("B3").Value = "" Then Exit Sub
If Range("B4").Value = "" Then Range("B4").Value = "None"
Sub Add_To_List()
If Range("B2").Value = "" Then Exit Sub
If Range("B3").Value = "" Then Exit Sub
If Range("B4").Value = "" Then Range("B4").Value = "None"
If Range("B5").Value = "" Then Range("B5").Value = "Unknown"
Leave cells B4 and B5 empty.
After clicking the button your worksheet should resemble this one.
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.