Solved: How Does VBA Handle Multiple Conditions Within an If Statement

[sam314159]

I am trying to figure out if VBA will exit an 'If' statement at the first sight of a 'false' condition when a few conditions are strung together using an 'And'.

My code looks like this:

If (test1 = true) And (test2 = true) Then

To me, if test1 = false I would think test2 condition would not even be checked. Seems like VBA is checking test1 and finding it false and still proceeding with test2.

Is there an option to make it stop doing that?

I know I can always nest If statements of course, but I still wanted to understand this a little better. Thanks guys.

[lynnnow]

Sam,

Since both your test conditions are on the same line, VBA will check to see what both tests return. If your final checker is set to quit the sub if either is false, then it will quit. For example

if (test1 = true and test2 = true) = true then the code will run

if (test1 = true and test2 = false) = true then the code should not run

similarly:

if (test1 = false and test2 = true) = true then the code should not run.

This is what my theory is, i have not tested it with any code.

HTH.

Lincoln

[sam314159]

I agree with you Lincoln, what I was thinking is if the first condition fails then I shouldn't even waste processor time evaluating the second test since there is an 'And' in between them.

I thought the sequence would be:

1. They both must be true
2. I know the first one is false
3. I don't care what the second one is

My test1 is a quick condition that filters out most false inputs, my test2 is a very thorough function that goes through multiple loops to determine if inputs are true or false and I don't want that to run every time.

I probably need to just use nested if statements.

[GTO]

Certainly just a laymen level opinion, but I cannot imagine any appreciable difference in run time, unless possibly, you had a notably large amount of conditions to check. Generally, it seems that IF checks are performed very quickly.

[Paul_Hossler]

If you have a lot of If-Then / ElseIf-Then .... a Select Case would inprove the readability, and possibly a slight increase in performance if it was being executed a gazillion times in a loop.

Paul

[sam314159]

Trust me GTO, no one here is closer to a laymen level in VBA programming than me

I think you are 100% correct, I am running it both ways and I can not see any difference in performance.

I thought the fact that my second condition was a pretty complex function that goes through multiple loops and calculations to get a result would slow things down.

Still kinda of curious as how it's really done and if I do undestand it correctly, I wonder why that's done.

That's like saying, my car won't run because it's on fire but I still need to check the tire pressure to see if I can drive it.

By the way, these kinds of threads are what you get when you let engineers with no programming experience run rampant in VBA forums!

[GTO]

If you have a lot of If-Then / ElseIf-Then .... a Select Case would inprove the readability, and possibly a slight increase in performance if it was being executed a gazillion times in a loop.

Paul

LOL, I just knew it was either a bajillion or gazillion, just couldn't recall which.

Trust me GTO, no one here is closer to a laymen level in VBA programming than me

I think you are 100% correct, I am running it both ways and I can not see any difference in performance.

I thought the fact that my second condition was a pretty complex function that goes through multiple loops and calculations to get a result would slow things down.

Still kinda of curious as how it's really done and if I do undestand it correctly, I wonder why that's done.

That's like saying, my car won't run because it's on fire but I still need to check the tire pressure to see if I can drive it.

By the way, these kinds of threads are what you get when you let engineers with no programming experience run rampant in VBA forums!

I'm not exactly sure what you are asking. How to nest IFs or ??? I certainly would look at Select Case as Paul mentioned.

No problems as to asking questions. Shucks, I have and continue to learn a lot here, and while I've probably hesitated from asking something I 'just know' is simple, when I do, even if I have to repeat the question (like: "Can you explain that again, only in realllly small words?"), someone almost always takes the time and is patient. It is a great place.

A great weekend to all,

Mark

[mdmackillop]

You can also use functions to contain your tests
[VBA]
Sub testing()
Dim a%, b%, c As Boolean
a = 3: b = 2
c = test1(a, b) And Test2(a, b)
MsgBox c
End Sub


Function test1(a, b) As Boolean
test1 = a > b
End Function

Function Test2(a, b) As Boolean
Test2 = (a + b) < 6
End Function

[/VBA]

[sam314159]

Thanks a lot guys.

[Paul_Hossler]

Unless the piece of code is a lenghty loop or a multiply nested series of loops, I'd opt for readability instead of trying to save a few CPU cycles. I'd use the suggestions above.


Paul

[Bob Phillips]

What you are describing is called short-circuiting, that is the If exits immediately a false condition is encountered. VB, and therefore by default VBA, does not short-circuit, it checks each and every condition regardless of the result of each conditiona already checked. There is no obvious reason why this is so, it was just how the original developers built VB.

As GTO says, in each instance it will probbaly not make a lot of difference in performance terms, but if you do have lots of multi-condition tests, and especially if the tests involve objects that have to be looked up, there will be an overall application slowdown. Nesting the conditions will avoid that overhead, but the problem there is that the levels of nesting might get unwieldy, which is where the approach that Malcolm introduced could be helpful. In reality, I doubt performance would ever be an issue, but you might want to consider it in terms of your overall code design - I don't like multiple condition Ifs.

The last thing to be aware of is that in some circumstances, because VB does not short-circuit, you may encounter a run-time error. If your application was looking at dependent conditions, whereby the second one could only exist f the first was true, it will error if the first is false. As an example, not a great example but you will get the idea, look at this code

[vba]

Dim ws As Worksheet
Dim myVar As Long

myVar = 17

If myVar > 20 And ws.Name = "Bob" Then

MsgBox "We have a match"
End If
[/vba]

Run this and you will get a runtime error.

Nest the conditions like so,

[vba]

Dim ws As Worksheet
Dim myVar As Long

myVar = 17

If myVar > 20 Then

If ws.Name = "Bob" Then

MsgBox "We have a match"
End If
End If
[/vba]

and you get no error.

All part of the rich tapestry of a programmer.

[sam314159]

What you are describing is called short-circuiting, that is the If exits immediately a false condition is encountered. VB, and therefore by default VBA, does not short-circuit, it checks each and every condition regardless of the result of each conditiona already checked. There is no obvious reason why this is so, it was just how the original developers built VB.

That's exactly what I was asking about, thanks for elaborating more on the topic Xld. I didn't know about the term "short-circuiting".

The last thing to be aware of is that in some circumstances, because VB does not short-circuit, you may encounter a run-time error. If your application was looking at dependent conditions, whereby the second one could only exist f the first was true, it will error if the first is false. As an example, not a great example but you will get the idea, look at this code

That's exactly how I discovered that VB didn't do it the way I thought it would.

Thanks again for everyone who chimed in.