The messages in this thread are so contorted, it is know if you truly
understand. I hope the following helps to fine-tune your understanding.
"Dan Thompson" wrote:
Bob = 42
IF Not Bob = 42 Then Exit Sub
(Condition will not execute because it is FALSE and the "IF" statement
will only execute code when the "IF" statement returns true)
First, the better way to write a numerical comparison like that is:
If Bob<42 Then Exit Sub
But arguably, perhaps you are using that as a paradigm for a logical
expression using Not.
Second, an IF statement consists of a conditional expression between "If"
and "Then", a THEN-clause, and an optional ELSE-clause or ELSEIF-clause.
If the conditional expression is true, the statements in the THEN-clause are
executed.
If the conditional expression is false, the statements in the ELSE-clause
are executed. You can think of an ELSEIF-clause as an abbreviation for an
IF statement in an ELSE-clause.
In either case, after executing the statements in the THEN-clause or
ELSE-clause, execution continues with the statement following the IF
statement, unless you have a GOTO or EXIT statement in the then-clause or
else-clause of course.
See the VBA help page for "if then else statement".
If one And two And three = True Then
MsgBox "All of the 3 conditions are true"
That syntax works only by accident because "one" and "two" are Boolean
variables. Aside: Using variable names like "one", "two" and "three" that
have values other than 1, 2 and 3 is poor form and extremely confusing. Get
out of that bad habit quickly.
Suppose you have variables Joe, Moe and Curly, and you want to test if all
three are equal to 42. The following would __not__ do that:
If Joe and Moe and Curly = 42 Then Msgbox "all are 42"
Test with Joe=40, Moe=41 and Curly=42. You should see that it incorrectly
displays "all are 42".
The IF statement above tests if Joe is TRUE and Moe is TRUE and Curly=42.
Joe and Moe are TRUE if they are any non-zero value, which is not the
intent.
The correct form of that statement is:
If Joe=42 And Moe=42 And Curly=42 Then Msgbox "all are 42"
Returning to your IF statement, if the variables x, y and z are Boolean
types, the simplest way to test if all 3 are TRUE is:
If x And y And z Then Msgbox "all are true"
There is no need to write "x = true". In a conditional expression, simply
"x" is equivalent.
And tests like "not z = false" give me a migraine :-). "Not z = false" is
the same as "z = true", which is the same as simply "z" in a conditional
expression. By the way, "not z = false" is also the same as "z < false",
although that still gives me slight headache :-).
Forgive me if I am stating what is now "obvious" to you. I notice that you
switched to tests like "x = false or y = false or ..." in your last attempt.
But as demonstrated below, that can be written simply as "not x or not y or
....".
For Count = 1 To 4
If Count = 1 Then one = False
If Count = 2 Then two = False
If Count = 3 Then three = False
There are 8 combinations to test, not 4. The following is how I would test
all combinations. You should copy-and-paste this example if you want to try
it.
Dim x As Boolean, y As Boolean, z As Boolean, i As Integer
Debug.Print "-----"
For i = 0 To 7
x = (i Mod 2 = 1)
y = (i \ 2 Mod 2 = 1)
z = (i \ 4 Mod 2 = 1)
Debug.Print i, x, y, z,
If x And y And z Then
Debug.Print "all are true"
ElseIf Not x Or Not y Or Not z Then
Debug.Print "at least one is false"
Else
Debug.Print "logic error!"
End If
Next i
You can substitute Msgbox for Debug.Print. But I think the Immediate Window
is a much better approach. Press ctrl-G to see the Immediate Window.
----- original message -----
"Dan Thompson" wrote in message
...
Thanks for the advice I fixed the problem and I realize it was to do with
the
order of logic in my code. I understand now that however you present your
condition in an "IF" statement the "IF" statement always hast to evaluate
to
be true in order for VBA to execute the code in the "IF" statement.
Bob = 42
IF Bob = 42 Then MsgBox "Happy Birthday" (Condition executes because it
is
TRUE that Bob is 42)
Bob =35
IF Not Bob = 42 Then Exit Sub (Condition executes because it is TRUE that
Bob is NOT 42)
Both If statements execute because they are evaluated to be true
Right ??
on the other hand
Bob = 42
IF Not Bob = 42 Then Exit Sub (Condition will not execute because it is
FALSE and the "IF" statement will only execute code when the "IF"
statement
returns true)
I think I got it :)
btw I looked at that link you sent me and it just made my head spin lol
too deep for me
Dan Thompson
"JLGWhiz" wrote:
I get all screwed around with the logic tables and have to use trial and
error when I get into testing multiple boolean results. Here is a site
that
explains where the problem comes from.
http://www.rwc.uc.edu/koehler/comath/21.html
The If statement has to capture the condition so that the result is true
to
make the command line execute, even if the result is negative. This
means
that you can check to see if it Is, or you can check to see if it is Not,
but you cannot check to see if it is maybe. So when you use the And or
the
Or operators in the criteria line of the If statement you need to be
aware
of what the logic is doing in evaluating the statement.
"Dan Thompson" wrote in message
...
Hey JLGWhiz
This is what I am trying to accomplish only I would like to do it in
the
shortest amount of code as possible. I think my code is a little long
for
this macro. here is the code
Sub test()
Dim one As Boolean, two As Boolean, three As Boolean
Dim Count As Integer
one = True
two = True
three = True
For Count = 1 To 4
If Count = 1 Then one = False
If Count = 2 Then two = False
If Count = 3 Then three = False
MsgBox "Count # " & Count
If one = False Or two = False Or three = False Then
MsgBox "One or more of the 3 conditions are false"
End If
If one And two And three = True Then
MsgBox "All of the 3 conditions are true"
End If
one = True
two = True
three = True
Next Count
End Sub
"JLGWhiz" wrote:
Also:
If Not one = True Or Not two = True Or Not three = False Then
MsgBox "One or more of the 3 conditions are false"
End If
"Dan Thompson" wrote in
message
...
The following code should pop up a message box but it is not doing
so
where
am I going wrong ?
Sub test()
Dim one As Boolean, two As Boolean, three As Boolean
one = True
two = True
three = False
If Not one = True And Not two = True And Not three = False Then
MsgBox "One or more of the 3 conditions are false"
End If
End Sub
Dan Thompson