Module 3 - Conditions and Loops
Lesson 3.2 - Logical Tests
Topic 3.2.3 - The Not Operator

The Not operator allows you to change a Boolean value (True or False) into the opposite value.  This may not immediately appear to be useful but it can allow you to write some logical tests in a slightly more elegant way, as this part of the lesson shows you!

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 Sample Workbook

Extract and open the file you downloaded from the Files Needed section above.  You'll find a version of a workbook that you have used several times previously in this module:

Example

As you've seen previously, clicking the button will add the details in column B to the table on the right.

You can find the code which is attached to the button in the VBE:

Sub

This subroutine runs when the button is clicked.

To demonstrate how to use the Not operator, we'd like to add some code which checks that the user has entered a valid date in cell B3:

Valid date

If cell B3 doesn't contain a date, we should prevent the data from being added to the list.

 

The IsDate Function

We can use the IsDate function to check if the value in cell B3 is a date.  Begin writing an If statement at the top of the existing subroutine:

IsDate

The IsDate function accepts any value and tests if it can be converted into a date.  It returns a Boolean value (True or False).

 

If the value passed in to the function is not a date, the function returns the value False.  We can use this to check if the value of B3 is not a date as shown below:

Test if false

You can test if the result of the IsDate function is False as shown here.

 

We can then add the instructions we want to perform if our condition has been met:

If

If the condition is met, we'll enter a message in cell A5 then exit from the subroutine. We can clear this message after the End If line.

 

Test that your code works by entering some text into cell B3 and then clicking the button on the worksheet:

Test

You should see a message appear in cell A5.

 

Using the Not Operator

Although our code works, the way we've written the logical test is a little inelegant.  When you're testing the result of a function which returns a Boolean value, such as IsDate, you don't need to explicitly test if the result is equal to True or False.

Suppose for a moment that we wanted to test if the value in B3 was a valid date.  We could change our code to test if the result of the IsDate function is equal to True:

Test True

This tests if the IsDate function returns True.

 

When you're using a function which returns a Boolean value, the = True part of the statement is not required.  We can shorten the If statement shown above to the one shown below:

Shortened

This version of the If statement does exactly the same as the version shown above but is shorter and easier to type.

 

In our case, we want something to happen when the IsDate function returns False.  Rather than testing for this value explicitly, we can use the Not operator as shown below:

Not

This version of the If statement is the same as testing if the result of the function is equal to False.

 

Change your code to match the If statement shown above and check that it still works in the same way as before:

Error

You can't add the details to the list unless you enter a date.

 

Check that your code works properly when you do enter a valid date:

Working

When you enter a valid date you can add the film to the list by clicking the button.

To practise using the Not operator:

  1. In the same subroutine, add a new If statement to check if the value in cell B4 is not a number.  Use the IsNumeric function to do this:

If Not IsDate(Range("B3").Value) Then

Range("A5").Value = "Enter a valid date"

Exit Sub

End If

 

If Not IsNumeric(Range("B4").Value) Then

  1. Add instructions to display a message in cell A5 and exit from the subroutine:

If Not IsDate(Range("B3").Value) Then

Range("A5").Value = "Enter a valid date"

Exit Sub

End If

 

If Not IsNumeric(Range("B4").Value) Then

Range("A5").Value = "Enter a valid number"

Exit Sub

End If

  1. Enter some text in cell B4 and check that you see the expected warning in cell A5 when you click the button:
Invalid number

The value in cell B4 must be a number if you want to add the film to the list.

 
  1. Save and close the workbook.
This page has 0 threads Add post