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.1 - If Statements

Topic 3.1.1 - Simple If Statements

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.

Files Needed

You can click here to download the file needed for this page.

Completed Code

You can click here to download a file containing the sample code.

The Example Workbook

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.

Creating a Simple If Statement

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:

Add a second If statement to check if cell B3 is empty and exit from the subroutine if so:

Sub Add_To_List()

If Range("B2").Value = "" Then Exit Sub

If Range("B3").Value = "" Then Exit Sub

Add a new statement to check if cell B4 is empty. If so, change the value of the cell to indicate that no review has been entered:

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"

Add a new statement to check if cell B5 is empty. If so, change the value of the cell to indicate that the reviewer is unknown:

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"

Enter a new film title and review date into the appropriate cells and clear the contents of cells B4 and B5:

Leave cells B4 and B5 empty.

Click the button to run the code and check that the values you expected appear in the correct cells:

After clicking the button your worksheet should resemble this one.

Save and close the workbook.

This page has 0 threads

Add a new post

MODULES

LESSONS

TOPICS

Files Needed

Completed Code

The Example Workbook

Creating a Simple If Statement

Head office

London

Manchester