Using Boolean logic in VB, AND, OR

[RandySS]

Does Excel's VBA support the use of Boolean logic, such as OR, or AND? For example, If ...Column = 1 OR 3 OR 8 Then...
Specifically, as used in If Then Else statement is most applicable.
Thanks!

 

[SkipVought]

Hi,

Please post VBA Code questions in forum707.

The answer is YES, but your syntax is incorrect!

But do not respond in this forum. Please repsond in forum707.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

Sorry, Where is my head

The correct syntax is

if Column = 1 OR Column =  3 OR Column =  8 then

But I'd use...

Select Case Column
  Case 1, 3, 8
    
end Select

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[RandySS]

Thank you!! Both methods you identify look interesting. I'll have to check out how "Select" works.
Best regards!

 

[SkipVought]

Is is not Select.

It is Select Case.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[kredlo]

This test procedure shows that your syntax was OK.
str01 gets set to 'A'.

Sub test()

    Dim int01 As Integer
    Dim str01 as string

    int01 = 5

    If int01 = 5 Or 6 Or 8 Then
        str01 = 'A'
    Else
        str01 = 'B'
    End If
End Sub

 

[SkipVought]

Unfortunately, kredlo, you know not what you do.

1. APOSTROPHYs do not delimit String Literals. Rather QUOTES...

str01 = "A"

2. The If statememt with be TRUE, not matter what value you assign to int01, because the [red]BOLD parts of the expression are ALWAYS TRUE[/red]...

    If int01 = 5 Or [red][b]6[/b][/red] Or [red][b]8[/b][/red] Then

and if ANY part of the elements of the boolean expression are TRUE, then the result of the boolean is TRUE.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[RandySS]

Much to my benefit, the wealth of informaiton continues....!
But... you lost me when you said the BOLD parts of the expressionare ALWAYS TRUE. I don't see any bold. I do see a red 6 and a red 8. Are they inferred bold? Are they therefore true becuase they're not declared? As I say, you lost me.

 

[HarleyQuinn]

Yes, the 6 and 8 are in both bold and red.

The reason that they are causing the True part of the statement to be executed is that they are valid numeric expressions that are non-zero (zero would evaluate as False) and are therefore evaluated as True.

The reason that the statement always passes is that what you are basically saying is:

If int01 = 5 Or [red]True[/red] Or [red]True[/red] Then...

You can test this yourself very easily in the immediate window, try seeing what the following statements output:

?cbool(6) or cbool(8)

Which will return True, and then try the zero value

?cbool(0)

Which is evaluated as False (the cBool function converts any valid string or numeric expression into a boolean return type).

Hope this helps

Andy
---------------------------------
[green]' Signature removed for testing purposes.[/green]

http://lessthandot.com

 

[TonyJollans]

Bold, red, whatever, you spotted it.

Ignoring his other mistake, if you code, as kredlo did:

If int01 = 5 Or 6 Or 8 Then

the syntax is, indeed, valid but it does NOT mean the same as:

If int01 = 5 Or [red]int01 = [/red]6 Or [red]int01 =[/red] 8 Then

.

I do not think now is the time for a full explanation of what is happening here, but as you asked, specifically ...

[blue][tt] If int01 = 5 Or 6 Or 8 ...[/tt][/blue]

is the same as:

[blue][tt] If int01 = 5 ...[/tt][/blue]
followed by:
[blue][tt] If 6 ...[/tt][/blue]
followed by:
[blue][tt] If 8 ...[/tt][/blue]

Just taking one of these as an example, [blue][tt]If 6[/tt][/blue], doesn't really make much sense by itself. What VBA has to do is to interpret it as either True or False, and it chooses to interpret it as True, and, so, whatever code is conditional upon it being true is always executed.

As I have already said, I do not propose to give a full explanation here, please just accept that VBA does not support implicit comparands and you must specify all conditions in full. By way of example of what you have yet to learn, try running this code:

[blue]Sub test()
    
    Dim int01 As Integer
    int01 = 7

    If int01 = (5 Or 6) Then
        MsgBox "Hmmm..."
    End If

End Sub[/blue]

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[RandySS]

Tony,
Thanks very much! Your explanation helps a lot, and isn't overwhelming.
Best regards,
Randy

 

[fumei]

Try this:

Sub test()
    
Dim int01 As Integer
int01 = 7

[COLOR=red]' this ALWAYS is true, thus the messagebox[/color red]
   If int01 = 5 Or [b]6[/b] Then
      MsgBox "Hmmm...seems to anything"
   End If
   
[COLOR=red]' this is NOT true as int01 = 7, so...[/color red]
   If int01 = 5 Or [b]int01 = 6[/b] Then
      MsgBox "Still?"
   Else
      MsgBox "No...it is NOT 5 or 6"
   End If
End Sub

Why is the first one always true? Because 6 is evaluated as True (it is non-zero). An OR evaluates the IF as True if any of the conditions are True.

In the second IF, instead of 6, it has int01 = 6. As int01 = 7, obviously both int01= 5 and int01 = 6 are False and thus the OR makes the IF False.

Gerry

 

[SkipVought]

All of which is why I prefer the Select Case structure...

Dim int01 As Integer

int01 = 3

Select Case int01
  Case 1, 3, 8
     msgbox "match"
   Case Else
     msgbox "no match"
end Select

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[TonyJollans]

Gerry,

The [blue](5 or 6)[/blue] (note the parentheses) in my example does not always evaluate True - I deliberately chose the example (without explanation) to highlight one of the, perhaps, more unexpected results waiting to catch the unwary.

Skip,

I like Select Case too. I find it suits most complex checks better than an If construct.



Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[SkipVought]

And another FYI in a similar vein.

Novice VB coders, assume that declarations in the same line, inherit the data type of the last declaration, like...

Dim i, j, k, l, m, n as integer

(probably old FORTRAN coders)

Actually, only n, has been declared an integer. All the others are variant by default.

Each variable must have an explicit data type in the declaration...

Dim i as integer, j as integer, k as integer, l as integer, m as integer, n as integer

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[fumei]

Tony: "The (5 or 6) (note the parentheses) in my example does not always evaluate True "

The IF statement evaluates to true. If it does not evaluate to True, then why does it display the messagebox?

    If int01 = (5 Or 6) Then
        MsgBox "Hmmm..."
    End If

Does not the instruction MsgBox "Hmmm..." only execute if the IF is true?

BTW: in this case, both the above with (5 Or 6) and the below:

    Dim int01 As Integer
    int01 = 7

    If int01 = 5 Or 6 Then
        MsgBox "Hmmm..."
    End If

with no parenthesis, BOTH display the messagebox. So I am not sure what exactly you were trying to demonstrate.

Skip: I totally agree. I use Select Case much more often that I use If, although really, I try to use either of them (Select Case or If) to fit the situation.

Gerry

 

[TonyJollans]

[blue]Sub test()
    
    Dim int01 As Integer
    int01 = 7  [green]' a carefully chosen number[/green]

    If int01 = (5 Or 6) Then  [green]' True[/green]
        MsgBox "Hmmm..."
    End If

End Sub[/blue]

[blue]Sub test()
    
    Dim int01 As Integer
    int01 = [b][red]5[/red][/b]  [green]' another carefully chosen number[/green]

    If int01 = (5 Or 6) Then  [green]' now False[/green]
        MsgBox "Hmmm..."
    End If

End Sub[/blue]

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[SkipVought]

(5 OR 6) = 7

in binary...
[tt]
(101 OR 110) = (111)
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

similarly

(5 AND 6) = 4

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[fumei]

    Dim int01 As Integer
    int01 = 5

' False...no message displayed
    If int01 = (5 Or 6) Then
        MsgBox "Hmmm..."
    End If

' True...message displayed
    If int01 = 5 Or 6 Then
        MsgBox "Hmmm..."
    End If

It is an OR and 6 is "true".

Gerry