# Logical operators

Tags

### This, that, or the other

Cthulhu runs an online store. It offers free shipping on orders over \$200, and to goats. Otherwise, shipping is 10% of the order total.

• sProductPrice = Cells(1, 2)
• tCustomerSpecies = Cells(2, 2)
• If sProductPrice > 200 Then
•   sShipping = 0
• Else
•   sShipping = sProductPrice * 0.1
• End If
• If tCustomerSpecies = "goat" Then
•   sShipping = 0
• Else
•   sShipping = sProductPrice * 0.1
• End If
• Cells(6, 2) = sShipping

Would that work?

To check, let's make a JavaScript version of the program. You can run JS in a browser. Here's a form that uses a JS version of the code above. Give it a try.

Shipping cost:

Try the form for these cases:

• Price under \$200, not a goat (maybe a dog, or a cat)
• Price under \$200, is a goat
• Price over \$200, not a goat
• Price over \$200, is a goat
Reflect

Does the code work?

If you were logged in as a student, the lesson would pause here, and you'd be asked to type in a response. If you want to try that out, ask for an account on this site.

Ethan

Sometimes. But not always. Like, when the order was \$400, and the species is cat, shipping should be zero, but it isn't.

This series of screen shots shows what's happening. When it starts, the amount is 400, and the species is cat, as you can see in the locals.

Press F8:

Good. It's a big order, so shipping is zero.

Then:

So far, so good. Next:

OK, testing for species.

Not a goat.

Uh oh.

Shipping is 40. We want it to be zero.

OK, I get it. Test for goatness overwrote whatever the amount test did. It's as if the amount test didn't even happen.

Right!

### One `If` to test two variables

We have two separate tests.

• If sTotal > 200 Then
•   sShipping = 0
• Else
•   sShipping = sTotal * 0.1
• End If
• If tCustomerSpecies = "goat" Then
•   sShipping = 0
• Else
•   sShipping = sTotal * 0.1
• End If

Somehow, we want to put them together into one.

• If sTotal is high or the customer is a goat, or both Then
•   sShipping = 0
• Else
•   sShipping = sTotal * 0.1
• End If

So shipping is free is the total is high, or the customer is a goat, or both.

We can do that with a logical operator, like this:

1. sProductPrice = Cells(1, 2)
2. tCustomerSpecies = Cells(2, 2)
3. If sProductPrice > 200 Or tCustomerSpecies = "goat" Then
4.   sShipping = 0
5. Else
6.   sShipping = sProductPrice * 0.1
7. End If

Notice the `Or` in the third line. It combines the tests in an `If`. The entire test in the `If` will be true, if `sProductPrice > 200` is true, or `tCustomerSpecies = "goat"` is true, or both.

Here's what `Or` does, with code like `test1 Or test2`:

`test1` `test2` `test1 Or test2`
True True True
True False True
False True True
False False False

Some more code examples.

• If tPet = "cat" Or tPet = "dog" Then
•     MsgBox "Lot of people have one of those."
• EndIf

Notice: You can use the same variable more than once in a test.

• If tPet = "cat" Or tPet = "dog" Or tPet = "bird" Then
•     MsgBox "Even more people have one of those."
• EndIf

You can use an many `Or`s as you like.

This won't work:

• If tPet = "cat" Or "dog" Or "bird" Then
•     MsgBox "Even more people have one of those."
• EndIf

It makes sense to us humans, but remember: computers are very stupid. You have to write out the `tPet =` bit each time.

### This `And` that

Let's say we ask the user for a month, as a number, like this:

• sMonth = InputBox("Birthday month (1-12)?")

We get:

Someone's going to type:

Ack!

Let's show different messages, depending on whether the month is valid or not. Here's some code that might work:

• sMonth = InputBox("Birthday month (1-12)?")
• If sMonth is between 1 and 12 Then
•     tMessage = "Thanks for the month"
• Else
•     tMessage = "Sorry, " & sMonth & " isn't a valid month."
• End If
• MsgBox tMessage

In the second line, we want a test that's is true when the month is between 1 and 12, otherwise it's false.

This would do it:

• sMonth = InputBox("Birthday month (1-12)?")
• If sMonth >= 1 And sMonth <= 12 Then
•     tMessage = "Thanks for the month"
• Else
•     tMessage = "Sorry, " & sMonth & " isn't a valid month."
• End If
• MsgBox tMessage

`And` is a logical operator, like `Or`. `And` is true, only if both parts are true.

`test1` `test2` `test1 And test2`
True True True
True False False
False True False
False False False

So, for...

• If sMonth >= 1 And sMonth <= 12 Then

... the test is true when both `sMonth >= 1` and `sMonth <= 12`.

For example, suppose `sMonth` is 3.

• 3 is greater than or equal to 1, so `sMonth >= 1` is true.
• 3 is less than or equal to 12, so `sMonth <= 12` is true as well.
• `sMonth >= 1 And sMonth <= 12` is True And True. True And True is True.

What about when `sMonth` is 13?

• 13 is greater than or equal to 1, so `sMonth >= 1` is true.
• 13 is not less than or equal to 12, so `sMonth <= 12` is false.
• `sMonth >= 1 And sMonth <= 12` is True And False. That's False.

This is called a range check, since it checks whether a value is in a certain range. You'll see more of it later.

Pattern

Check whether a number is between two other numbers.

Warning: "range" has two meanings. This pattern is not about `Range` objects.

### Testing strings with `Or`s

Remember this, from earlier?

• If tPet = "cat" Or tPet = "dog" Or tPet = "bird" Then
•     MsgBox "Even more people have one of those."
• EndIf

Testing strings can be tricky. What's something we might want to allow for?

Georgina

Wouldn't you have to allow for extra spaces, and uppercase?

Right! We might want to do that. I could write:

• If Trim(LCase(tPet)) = "cat" Or Trim(LCase(tPet)) = "dog" Or Trim(LCase(tPet)) = "bird" Then
•     MsgBox "Even more people have one of those."
• EndIf

That would work, but is messy. It would be easier if I created a temporary variable.

• tNormalizedPet = Trim(LCase(tPet))
• If tNormalizedPet = "cat" Or tNormalizedPet = "dog" Or tNormalizedPet = "bird" Then
•     MsgBox "Even more people have one of those."
• EndIf

Normalizing a variable means converting it to a known format. In this case, all lowercase, with no leading or trailing spaces. Normalizing makes variables easier to test.

Pattern

Convert a variable that can have many formats, into one format.

That second `If` is kind of long.

Yes, it is. I'd break it up with line continuation characters.

• tNormalizedPet = Trim(LCase(tPet))
• If tNormalizedPet = "cat" _
•   Or tNormalizedPet = "dog" _
•   Or tNormalizedPet = "bird" Then
•     MsgBox "Even more people have one of those."
• EndIf

Easier to read, means easier to think about, write, debug, and change. Easy is good.

### `Not`

We've seen `And`, and `Or`. The last logical operator we'll talk about is `Not`. `And` and `Or` connect two tests. `x And y`. `x Or y`. `Not` just has one value: `Not x`. It changes true to false, and false to true.

Suppose Cthulhu wanted to sell to goats only.

• If Not tNormalizedSpecies = "goat" Then
•   MsgBox "Sorry, only goats are allowed."
• EndIf

• If Not (tNormalizedSpecies = "goat") Then
•   MsgBox "Sorry, only goats are allowed."
• EndIf

Let's suppose that goats and dogs are allowed to bake Curvois cakes, and nobody else. We want to show an error message for all other species.

• ' Normalize
• tSpeciesNormalized = Trim(LCase(tSpecies))
• If Not (tSpeciesNormalized = "goat" Or tSpeciesNormalized = "dog") Then
•     MsgBox "Sorry, you're not allowed to bake Curvois cakes."
•     End
• EndIf

There's a new statement there: `End`. `End` stops your program immediately.

`End` has nothing to do with `EndIf`. `End` end `EndIf` are as related as ham and hamster. That is, not at all.

​End and EndIf are as unrelated as hamster and ham

Here's the code again.

• ' Normalize
• tSpeciesNormalized = Trim(LCase(tSpecies))
• If Not (tSpeciesNormalized = "goat" Or tSpeciesNormalized = "dog") Then
•     MsgBox "Sorry, you're not allowed to bake Curvois cakes."
•     End
• EndIf

Suppose `tSpecies` is "goat". In that case, we don't want to show the error message. We want the `If` to skip the code inside it, and jump to `EndIf`.

So if `tSpecies` is "goat", then `tSpecies = "goat" Or tSpecies = "dog"` will be true. `Not (tSpecies = "goat" Or tSpecies = "dog")` will be false. The `If` statement will skip to `EndIf`, not showing the error message. That's what we want.

Now, suppose `tSpecies` is "sloth". In this case, we want to show the error message, and stop the program. If `tSpecies` is "sloth", then `tSpecies = "goat" Or tSpecies = "dog"` will be false. `Not (tSpecies = "goat" Or tSpecies = "dog")` will be true. That satisfies the `If`, which runs the code inside it, showing the error message, and stopping the program. That's what we want.

Ray

This is tricky to think about.

A little, but run through it a few times, and it will make sense.

Here's the `If` again:

• If Not (tSpecies = "goat" Or tSpecies = "dog") Then

You could also write it this way:

• If tSpecies <> "goat" And tSpecies <> "dog" Then

If `tSpecies` doesn't equal goat, and `tSpecies` doesn't equal dog, then show the error message, and stop.

The second one seems a little clearer to me, but not to everyone. Use whichever one makes the most sense to you.

### Precedence

Remember operator precedence?

Fill in the blank

Expression value

What is sX after this runs?

• sX = 2 + 3 * 4

Not graded. So why do it?

For the arithmetic operators, `*` has a higher precedence than `+` or `-`, so is done first.

For the logical operators, `Not` has a higher precedence than `And` or `Or`, so is done first.

What does that mean? Here's one version of the code we just looked at, with an error.

• ' Normalize
• tSpeciesNormalized = Trim(LCase(tSpecies))
• ' Should be If Not (tSpecies = "goat" Or tSpecies = "dog") Then
• If Not tSpecies = "goat" Or tSpecies = "dog" Then
•     MsgBox "Sorry, you're not allowed to bake Curvois cakes."
•     End
• EndIf

`Not` has a higher precedence that `Or`, so Excel does `Not tSpecies = "goat"` first, and `Or`s that with `tSpecies = "dog"`. So if `tSpecies` is "dog", the `If` statement will show the error message (trace it through). Not what we want!

If we want to change the order, of calculation, we use (), so the `Or` is done first, and then the `Not`.

This is tricky at first. The debugger is helpful in figuring out whether your code is working. You could put a breakpoint on the `If`, try the code with "goat", then "dog", and then "sloth". See if what happens matches what you want to happen.

You can make a quick table to help keep things straight:

Test result table

You write down different inputs to test, what you expect your program to do with each input, and what actually happens. Takes just a moment, and lets you make sure your `If` is right. Easier on your brain, and your mood.

### Exercise

Exercise

Is a TMNT?

Write a program that tells you whether a name is a TMNT. The user types a name...

... and clicks the button. In this case, the program would show:

On the other hand:

Write the code so that case doesn't matter, and the program would still work if the user types extra spaces at the beginning or end of the cell. So:

The TMNTs are:

• Leonardo
• Michaelangelo
• Donatello
• Raphael