Logical operators

VBA supports three logical operators: And, Or, and Not. They let you combine two or more comparison tests into a single compound comparison. For example, suppose we wanted to implement the following code:

If (myCell.Value =6) Then If (Activecell.Value>0) Then

MsgBox "Done" End If End If

The two comparison tests can be replaced with one test as: If (myCell.Value =6) And (ActiveCell.value >0) Then.

Table 6.3 The logical operators


Example Usage



If (myCell.Value=6) And (z<0)

Succeeds, or produces True, if both sides of the And are true. In this example myCell.Value must be 6 And z must be less than 0. Otherwise, the expression fails.


If (myCell.Value=6) Or (z<0)

Produces True if either side of the Or is true - in this example myCell.Value must be 6 Or z must be less than 0. Otherwise, the expression produces false.


If (Not x=5)

Negates the result, turning true to false and vice versa. In this condition checks to see if x is not = 5.

If Not

If Not IsNumeric(cell)

For this example, if the cell is not numeric the true result is returned, else the result is false.

As you can see from Table 6.3, the logical operators let you combine more than one comparison test in a single If statement. The Not negates a comparison test. You can often turn a Not condition around. For example, the condition If (Not x = 5) is equivalent to If (x <> 5). Not can produce difficult comparison tests, and you should use it cautiously. In the second example in the table, it produces a more effective result than the alternatives.

0 0

Post a comment