Logical Operators

 

Logical Operator And    |   Logical Operator Or    |   Logical Operator Not

In all sorts of macros we use Excel VBA logical operators. The three most used logical operators in Excel Visual Basic are: And, Or and Not. Each logical operator will now be illustrated with the help of an easy example.

Logical Operator And

For example, we hire a person for a certain job, only if his/her IQ quotient is higher or equal to 110 and the impression he or she leaves during an interview is higher or equal to 7 (on a scale of 1 to 10).

Logical Operator And Example

Place a command button on your worksheet and add the following code lines:

If Range("B1").Value >= 110 And Range("B2").Value >= 7 Then
    Range("B4").Value = "Hire"
Else
    Range("B4").Value = "Do not hire"
End If

Result when you click the command button on the sheet:

Logical Operator And Result

The macro uses the logical operator And to get the right result. We do not hire the person because his/her IQ quotient is lower than 110.

Logical Operator Or

For example, we hire a person for a certain job, only if his/her IQ quotient is higher or equal to 110 or the impression he or she leaves during an interview is higher or equal to 7 (on a scale of 1 to 10).

Code:

If Range("B1").Value >= 110 Or Range("B2").Value >= 7 Then
    Range("B4").Value = "Hire"
Else
    Range("B4").Value = "Do not hire"
End If

Result:

Logical Operator Or Result

This example shows you the effect of the Or statement. Although, the IQ quotient of the person is way below 110, we hire the person because he or she leaves a very good impression during the interview.

Logical Operator Not

For example, we do not hire a person for a certain job, who is 'greedy' and 'all about the money'. The macro below uses the logical operator Not to get the right result. The Not operator designates that the condition must not be true.

Slightly adjust the previous macro. Code:

If (Range("B1").Value >= 110 Or Range("B2").Value >= 9) And Not Range("B3").Value = "Yes" Then
    Range("B4").Value = "Hire"
Else
    Range("B4").Value = "Do not hire"
End If


Result:

Logical Operator Not Result

We want one of the first two conditions to be true (use brackets) and the third condition must not be true. We do not hire the person because the third condition is true (cell B3 contains the word Yes).

You can debug through the code using F8 (see picture below). Place the cursor on the code to see the actual values! The '_' symbol is used here to continue the statement on a new line (not necessary). Always test your macro when you use logical operators to be sure that they do what you want.

Debug your Code

Did you find this information helpful? Show your appreciation, vote for us.

Go to Top: Logical Operators    |    Go to Next Topic: Range

Learn more about logical operators, Login to the right >>
100 easy to follow Excel VBA examples. Limited time-offer: $39.00 but only $29.00. Ends on 29th February.