Understanding And/Or logic VBA

Question

I'm so confused about why the second If statement works but the first one does not.

The actual result I want is if the user does not enter Peach or Banana or Mango then show the message.

The Or's in the sentence above makes the most sense. However, if I said "if the user does not enter Peach and Banana and Mango" then show the message - It sounds like you would need to enter all three fruits to show the message which is not the result I'm looking for. But! It's the one that works.

The second If statement shows the message if none of the correct fruit is entered however, the first statement does not work at all.

Please help clarify.

Column A	Column B
Cell 1	Apple
Cell 2	Orange

If Item.Value <> "Peach" or Item.Value <> "Banana" or Item.Value <> "Mango" Then
   MsgBox "Fruit Not Available"
End If

If Item.Value <> "Peach" And Item.Value <> "Banana" And Item.Value <> "Mango" Then
   MsgBox "Fruit Not Available"
End If

I tried both statements but only the second one works and I'm confused why that is.

You have `If x <> 1 Or x <> 2`. X is equal to 2. What will the `If` evaluate to? — GSerg, Mar 06 '23 at 19:28
https://stackoverflow.com/a/913963. `!(a or b)` = `(!a) and (!b)`. This is just logic. — BigBen, Mar 06 '23 at 19:29
@BigBen The link you provided says !(a or b) = Not a nor b which is what I want but the OR doesnt give me this the AND statement does — Deon Bowers, Mar 06 '23 at 22:03
Correct. What that link says is that `!(a or b)` is equvalent to `(!a) and (!b)`. It is not equivalent to `(!a) or (!b)`, which is your first (incorrect) attempt. Your second attempt, where you use `and`, is correct. Note the placement of parentheses too. — BigBen, Mar 06 '23 at 22:06

Aldert · Accepted Answer · 2023-03-06T19:54:17.000

VBA will evaluate each of the comparisons to true/false

Lets assume the Item.Value = "Peach"

so we get:

false = Item.Value <> "Peach"
true = Item.Value <> "Banana"
true = Item.Value <> "Mango"

Having this in the first or will give you:

true = false or true or true

Regardsless if the value is any of the fruits or not, it will give you always true and will not work.

Having this in the second AND will give you:

false = false AND true AND true

This is when one fruit is a match. If Item.Value = "Apple" you will get:

true = true  AND true AND true

for you this results in "the second statement works" where as they both work correctly but the first has no value for you.

Understanding And/Or logic VBA

1 Answers1