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.2 - Logical Tests

Topic 3.2.1 - Combining Conditions - Or

You've written many If and ElseIf statements so far in this module, but each one you have written has only tested a single condition. In this part of the lesson, you'll learn how to combine multiple conditions into a single test using the Or operator.

The Or operator is an example of a logical operator. You'll learn about two other commonly-used logical operators, the And and Not operators, in the next two parts of this lesson.

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.

The Example Workbook

Extract and open the file that you downloaded from the Files Needed section above. You'll find a workbook that is similar to an example you used in an earlier part of this module:

You can enter details of a film you have watched in the cells in column B, then click the button to add those details to the table on the right.

You can find the subroutine that is assigned to the button in the VBE:

This subroutine runs when you click the button on the worksheet.

We'll add code to this procedure to ensure that the user has entered sensible values into the cells in column B before the data is copied into the table.

Creating the Basic If Statement

We'll start by adding a basic If statement which checks if cell B2 is empty:

Add an If statement at the start of the subroutine.

If this condition is met, we'll display a message in cell A5 and then exit from the subroutine. Complete the If statement as shown below:

The basic If statement should look like this.

You can test that this works by clearing the contents of cell B2 and then clicking the button on the worksheet:

If you don't enter a value in B2, nothing will be added to the table.

This works, but what if the user did enter a title but missed out either of the other two values? We could write a separate If statement for each of the three cells, but there may be a better solution.

Adding a Condition to an If Statement

Rather than writing a separate If statement to check each cell, let's add an extra condition to the existing If statement. We'll edit the code so that it checks if either cell B2 or B3 is empty:

Start by adding the word Or before the Then keyword at the end of the line.

You can now write another logical test to check if cell B3 is empty:

You now have two logical tests in the same If statement.

You may wish to add a line which clears the error message from cell A5 when we have filled in all the values:

Add a line below the End If statement as shown here.

Using the Or operator means that if either of the two conditions is met, the entire logical test returns True. Test that your code works when you leave either cell B2 or B3 (or both) empty:

If you miss out the title or date, you can't add the film to the list.

Using Multiple Lines

You can continue doing this to add as many conditions as you like, although the code can become difficult to read if you add too much to a single line. You may prefer to use continuation characters to break one long line into several smaller ones:

Type a space followed by an underscore after the first condition, then write the second condition on the next line.

This makes it easier to add new conditions to the same If statement. For example, we should also check that the user has entered a score for the film:

Add a space and underscore after the second condition and then write a third condition on the next line.

When you test the code you'll find that if any of the three conditions in the If statement are met, you'll be prevented from adding the film to the list:

If you miss out any of the three values, you won't be able to add the film to the list.

Make sure that your code still works when you do fill in all of the required values:

If you enter the three required details you can click the button to add a row to the table.

To practise combining conditions with the Or operator we'll add code to check that the user has entered valid values in cells B3 and B4:

Using the same subroutine, add a new If statement below the existing End If to check if the value in B3 is a date:

If IsDate(Range("B3").Value) = False Then

End If

The IsDate function returns True when the value you pass into it can be successfully converted into a date.

Add instructions to display a message in cell A5 and then exit from the subroutine if the condition is met:

If IsDate(Range("B3").Value) = False Then

Range("A5").Value = "Wrong data type"

Exit Sub

End If

Use the Or operator to add a second condition to the If statement to check if the value of cell B4 is a number:

If IsDate(Range("B3").Value) = False _

Or IsNumeric(Range("B4").Value) = False Then

Range("A5").Value = "Wrong data type"

Exit Sub

End If

The IsNumeric function returns True when the value you pass into it can be successfully converted into a number.

Test that your code works:

If you enter a value of the wrong type in either cell B3 or B4 you'll see an error message in cell A5.

Next, we'll check that the value in B4 is between 0 and 10. Add a new If statement below the one you have just created and check if the value of B4 is less than 0 or greater than 10:

If Range("B4").Value < 0 _

Or Range("B4").Value > 10 Then

Range("A5").Value = "Score out of range"

Exit Sub

End If

Check that the code works if you enter a number outside the valid range:

You can't add a film to the list if the score is not between 0 and 10.

Check that your code works when you enter valid details for a film and then 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 the Basic If Statement

Adding a Condition to an If Statement

Using Multiple Lines

Head office

London

Manchester