Excel Visual Basic for Applications online training

This page has 1 thread | 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.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:

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:

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:

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:

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:

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 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:

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:

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:

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:

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:

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:

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

To practise using the Not operator:

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

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

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

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

Save and close the workbook.

This page has 1 thread

Add a new post

From:	Kayvan
When:	17 Sep 20 at 16:42

Hi Andrew,

Many thanks for your previous response.

Can we write our code like the following for this exercise:

If Not IsDate(Range("B3").Value) _
And Not IsNumeric(Range("B4").Value) Then
Range("A5").Value = "Enter a valid data"
Exit Sub
End If

If we can, why do I receive the wrong result?

Sincerely

Kayvan

From:	Andrew G
When:	18 Sep 20 at 08:49

Hi Kayvan, you can write it like that but it won't behave exactly as I think you want it to. If you use the AND operator, your message will appear in A5 only if B3 is not a date AND B4 is not a number at the same time. If only B3 or B4 has an invalid value, your message won't appear. Try replacing the AND operator with the OR operator instead.

I hope that helps!

From:	Kayvan
When:	18 Sep 20 at 15:06

Thank you so much.

Kayvan

Add a new post

MODULES

LESSONS

TOPICS

Files Needed

Completed Code

The Sample Workbook

The IsDate Function

Using the Not Operator

Head office

London

Manchester