You've seen numeric and string expressions. There's one more type of expression. It's mainly used in If statements.

If [logical expression] Then

A logical expression is either true or false.

Operators

There are two types of operators we need to talk about. First, there are comparison operators.

<	Less than	`2 < 4` is `True`
<=	Less than or equal to	`33 <= 12` is `False`
=	Equal to	`(2 + 3) = 5` is `True`
<>	Not equal to	`17 - 10 <> 5 * 2` is `True`
>	Greater than	`13 > (5 + 11)` is `False`
>=	Greater than or equal to	`(15 / 3) >= (4 + 1)` is `True`

Notice that the comparison operators compare expressions. Other examples:

Sqr(length) < Sqr(width)

daBears < daLions

jenniferLawrenceLikes / 2 > kanyeWestLikes * 2

The last one shows that you really don't need parentheses (the "()") around numerical expressions when you use them in logical expressions. Numerical operators (+, -, etc.) have a higher precedence than comparison operators. Still, add parentheses if it makes things clearer for you.

You can also compare strings. Comparison is done in lexical order; kind of like alphabetical order, but with additions for upper- and lowercase letters, and special characters. So:

`"A" < "M"` is `True`	Alpha comparison.
`"Cut" < "Dig"` is `True`	First character is different, so the rest don't matter.
`"Docu" > "Dohe"` is `False`	First two characters are the same, so the comparison is based on the third.
`"a" > "Z"` is `True`	Lowercase characters are greater than all uppercase.
`"123 Smith St." < "17 Smith St.` is `True`	Character comparison, not numeric. Comparison stops at the second character
`" " < "A"` is `True`	Space is a character, and is less than any letter.
`"#" < "A"` is `True`	`#` is a special character.
`"~" < "A"` is `False`	Special characters are weird.

There are also logical operators.

Not	Flips true to false, and the reverse	`Not True` is `False`
And	True if both are true	`True And False` is `False`
Or	True if either or both are true	`True Or False` is `True`

The logical operators have a lower priority than any of the others. Within the logical operators, Not is highest, then And, then Or.

Some people get the priority wrong. Suppose the user types in a new lion cub name. Anything is OK, unless it's Simba or Nala. So if the new name is not Simba, and it's not Nala, it's OK.

Check out this code:

cubName = Cells(2, 2)
if Not cubName = "Simba" And cubName = "Nala" Then
    MsgBox "Name OK."
EndIf

Doesn't work, because Not has higher precedence than And. The Not is applied to the first part of the test (cubName = "Simba"), but not the second.

This would work:

cubName = Cells(2, 2)
if Not cubName = "Simba" And Not cubName = "Nala" Then
    MsgBox "Name OK."
EndIf

So would this:

cubName = Cells(2, 2)
if Not (cubName = "Simba" And cubName = "Nala") Then
    MsgBox "Name OK."
EndIf

Jeremy

Ouch! That hurt my brain.

Tara

It's messy when you're not used to it.

When in doubt, use parens, like the last Simba and Nala example.

If you add parens to code that doesn't really need them, that's OK. Better to be sure, than scream at your computer.

Examples

Make sure a cell has a positive number

If Cell(1, 2) <= 0 Then
    MsgBox "Sorry, cell B1 must have a positive number."
EndIf

If Not ( Cell(1, 2) > 0 ) Then
    MsgBox "Sorry, cell B1 must have a positive number."
EndIf

If Not Cell(1, 2) > 0 Then
    MsgBox "Sorry, cell B1 must have a positive number."
EndIf

Not has a lower priority than >, so the parentheses are not needed. But you should use them if it makes things clearer to you.

Make sure a cell has a number between 0 and 10

If Cell(1, 2) < 0 Or Cell(1, 2) > 10 Then
  MsgBox "Sorry, Cell B1 should be between 0 and 10."
EndIf

groupMembers = Cell(1, 2)
If groupMembers <  0 Or groupMembers > 10 Then
  MsgBox "Sorry, Cell B1 should be between 0 and 10."
EndIf

Klaus

What's the difference between the last two? Why would you put the value in a variable and then check it?

Tara

Two reasons. The first is documentation. The name of the variable reminds you what data is being stored in the cell.

The second reason is ease of maintenance, that is, making the code easier to change. Suppose you had to add two new rows at the top of the worksheet. Cells(1, 2) would be changed to Cells(3, 2). In the first code sample, you have two changes to make. In the second code sample, there's only one. That means a lower chance of mistakes.

groupMembers = Cell(1, 2)
If Not ( groupMembers >=  0 And groupMembers <= 10 ) Then
  MsgBox "Sorry, Cell B1 should be between 0 and 10."
EndIf

The parentheses are needed, since Not has a higher priority than And. You could also do this:

groupMembers = Cell(1, 2)
If Not groupMembers >=  0 And Not groupMembers <= 10 Then
  MsgBox "Sorry, Cell B1 should be between 0 and 10."
EndIf

Make sure a cell has Yes or No in it

taxable = Cells(4, 4)
If taxable <> "Yes" And taxable <> "No" Then
    MsgBox "Sorry, taxable should be Yes or No."
EndIf

Better version:

taxable = LCase(Cells(4, 4))
If taxable <> "yes" And taxable <> "no" Then
    MsgBox "Sorry, taxable should be Yes or No."
EndIf

The user can type "Yes", "yes", "YES", "No", "NO", etc.

taxable = LCase(Left(Cells(4, 4), 1))
If taxable <> "y" And taxable <> "n" Then
    MsgBox "Sorry, taxable should be Yes or No."
EndIf

Recall that Left("YES", 1) takes one character from the left. So the user can type "Yes", "YES", "y", "Y", "YOLO", "Night Stalker", etc.

taxable = LCase(Left(Cells(4, 4), 1))
If Not (taxable = "y" Or taxable = "n") Then
    MsgBox "Sorry, taxable should be Yes or No."
EndIf

taxable = LCase(Left(Trim(Cells(4, 4)), 1))
If Not (taxable = "y" Or taxable = "n") Then
    MsgBox "Sorry, taxable should be Yes or No."
EndIf

Trim() removes spaces from the beginning and end of a string. When people cut-and-paste, it's easy for extra spaces to get in by mistake.

Jeremy

Wow, LCase(Left(Trim(Cells(4, 4)), 1)). That makes my brain hurt.

Lily

Yeah, me too.

Tara

Me three. It's hard to understand, and that makes it hard to debug, and change if necessary. I would break that one up. Maybe like this:

taxable = Trim(Cells(4, 4))
'Use the first char only.
taxable = Left(taxable, 1)
'Allow upper- and lowercase.
taxable = LCase(taxable)

The comments also help.

Exercises

Fill in the blank

Comparing attributes

Here's some code:

Dim pHp As Integer
Dim pAc As Integer
Dim mHp As Integer
Dim mAc As Integer
pHp = 23
mHp = 22
pAc = 8
mAc = 9
If pHp > mHp And Not pAc < mAc Then
    MsgBox "Player advantage"
Else
    MsgBox "Monster advantage"
End If

What message will the user see?

Work it out on paper first, before you try it in Excel.

Your answer:

Exercise: Is that a Beatle?

Write a program to tell whether a person is a Beatle. Here's the worksheet:

Start

The user types a name in B1, and clicks the button. If the name if John, Paul, George, or Ringo, the program says that the person is a Beatle:

Output

Otherwise, the program shows "Not a Beatle."

The program should work for upper- and lowercase characters, and when there are spaces before and/or after the name. For example, " jOHn " should be identified as a Beatle.

The usual coding standards apply.

Upload your solution.

(If you were logged in as a student, you could submit an exercise solution, and get some feedback.)

Summary

Logical expressions are either true or false. They are mainly used in If statements.

Comparison operators like <= and <> compare two numeric or string values, and are either true or false. Not, And, and Or combine the results of logical expressions, like empCount >= 1 And empCount <= 6.

There are many ways to write the same If test. Use whatever is easiest for you to understand.

If statements and logical expressions

Operators

Examples

Make sure a cell has a positive number

Make sure a cell has a number between 0 and 10

Make sure a cell has Yes or No in it

Exercises

Summary