CODEWITHSUNDEEP
excelvba
W-hit
W-hit

Reputation: 361

Why does 'AND' work here when 'OR' does not?

In my situation, the use of OR does not work, I have to use AND, and I'm not sure why.

Sub blah()
    If LCase(Environ("username")) <> "me" Or LCase(Environ("username")) <> "some other user" Then '''DOESN'T WORK
        Stop
    ElseIf LCase(Environ("username")) <> "me" And LCase(Environ("username")) <> "some other user" Then  '''WORKS
        Stop
    End If
End Sub

Upvotes: 1

Views: 86

Answers (4)

barrowc
barrowc

Reputation: 10679

This can be explained using DeMorgan's laws. If we take:

If LCase(Environ("username")) <> "me" Or LCase(Environ("username")) <> "some other user" Then

then we can rewrite this by replacing the <> with = and then negating each term with Not:

If Not(LCase(Environ("username")) = "me") Or Not(LCase(Environ("username")) = "some other user") Then

Using DeMorgan's laws, we can take apply a single negation to the whole expression as long as we change the Or operator to an And operator:

If Not(LCase(Environ("username")) = "me" And LCase(Environ("username")) = "some other user") Then

It's impossible for LCase(Environ("username") to simultaneously equal two distinct values and thus the And condition will be False. Applying the Not operator means that the entire expression will always evaluate to True regardless of the value of "username"

For the corrected version using And instead of Or the steps are as follows:

If LCase(Environ("username")) <> "me" And LCase(Environ("username")) <> "some other user" Then

Change <> to = and apply Not operators:

If Not(LCase(Environ("username")) = "me") And Not(LCase(Environ("username")) = "some other user") Then

Apply DeMorgan's laws:

If Not(LCase(Environ("username")) = "me" Or LCase(Environ("username")) = "some other user") Then

Now, if the username is "me" or the username is "some other user" then the Or condition will evaluate to True and the Not operator will change that to False as required.

If the username is any other value then the Or condition will evaluate to False and the Not operator will change that to True as required

Upvotes: 0

user11477650
user11477650

Reputation: 16

The VBA Or doesn't work because when Environ("username") is me, it isn't some other user and vice versa.

You want to make sure that it isn't either of the possibilities so you need And.

Upvotes: 0

HackSlash
HackSlash

Reputation: 5803

Let's break it down. I have to assume that "me" is your own username. The result of this test is: False, True, True

Debug.Print LCase(Environ("username")) <> "me"
Debug.Print LCase(Environ("username")) <> "some other user"
Debug.Print LCase(Environ("username")) <> "me" Or LCase(Environ("username")) <> "some other user"

That is the expected behavior to me.

Upvotes: 0

Mathieu Guindon
Mathieu Guindon

Reputation: 71187

Your example is hard to understand, but you're probably just confused by the nature of the operators.

Think of truth tables; the result of an Or operation is True if at least one of the operands is True:

True Or True   => True
True Or False  => True
False Or True  => True
False Or False => False

Similarly, the result of an And operation is True if both operands are True:

True And True   => True
True And False  => False
False And True  => False
False And False => False

If it helps, you can visualize Or as a + (addition) and And as a * (multiplication) operation, with False being 0 and True being "not zero":

1 Or 1 => 1 + 1 => True
1 Or 0 => 1 + 0 => True
0 Or 1 => 0 + 1 => True
0 Or 0 => 0 + 0 => False

1 And 1 => 1 * 1 => True
1 And 0 => 1 * 0 => False
0 And 1 => 0 * 1 => False
0 And 0 => 0 * 0 => False

Upvotes: 7

Related Questions