Which if statement performs faster in VBA?

Question

In VBA, which of the following if statements will perform better?

Context: I'm iterating through a sheet looking for an entry.

if [condition1] and [condition2] and [condition3] then
    *do something*
end if

or

if [condition1] then
    if [condition2] then
        if [condition3] then
            *do something*
        end if
    end if
end if

My intuition says the second statement, as it only needs to check one initial condition before proceeding to the next iteration, but is this so? Also, is there a better way of doing this?

Answer 1

We should forget about small efficiencies, say about 97% of the time: premature optimization is the root of all evil. Yet we should not pass up our opportunities in that critical 3%.

— Donald Knuth

Assuming none of the conditions have side-effects and essentially boil down to this:

If True And True And False Then

Then you're over-thinking it and are likely over-optimizing without any meaningful performance gains. Stop trying to squeeze out every nanosecond from your code, and write code that's easy to read, maintain, debug, and extend instead: if there's a performance bottleneck anywhere in your code, it's not here.

---

Context: I'm iterating through a sheet looking for an entry.

See, iterating through a sheet looking for an entry is your bottleneck. Interacting with a worksheet is hundreds if not thousands of times slower than interacting with an in-memory array.

So yes, because you're writing expensive code, lack of short-circuiting will make a difference, but not because short-circuiting is more a efficient use of Boolean logic - it will make a [potentially significant] difference because you'll be conditionally avoiding expensive worksheet reads.

If that sounds contradictory, you're not getting my point: the performance impact of short-circuiting Boolean logic on code that's already efficient, is largely insignificant.

Go on, try dumping the Range you're iterating (and are you doing that with a For or a For Each loop? There's a massive difference between the two, depending on what you're doing) into a 2D array, and then use a For loop to iterate it and look for your entry. I promise a much more noticeable performance improvement than anything you could get out of splitting your conditionals to simulate short-circuiting.

Or, perhaps re-evaluate whether you need to iterate anything at all. If you're looking for an entry, and depending on what your data looks like, you could combine the "condition columns" into one and use Application.WorksheetFunction.Match to locate an entry without writing any loops or conditions.

Answer 2

Here's link that might be helpful.

Generally, second would perform faster for simple reason: if first condition fails, it won't check other two. In first approach it has to evaluate all three conditions. In other programming languages you have short-circuiting, which you implemented in second approach (in VBA it has to be done this way, since there's no short-circuiting).

Answer 3

The 2nd format would be the faster implementation.

You can see that vba does not short circuit evaluations by trying the following commands in the immediate window:

if 1=0 and 1/0 then ?"Short Circuit"
if 1=1 or 1/0 then ?"Short Circuit"

If vba operated like some other languages, then you would get the message Short Circuit printed. Instead, you get the error:

Of course, I would refer you to Mat's Mug's answer, as reading a range into memory operates a lot faster than reading a sheet directly.

I will refer you to my answer to another question on how to read/write arrays and cells