Does the VBA "And" operator evaluate the second argument when the first is false?

Function Foo(thiscell As Range) As Boolean
  Foo = thiscell.hasFormula And (InStr(1, UCase(Split(thiscell.formula, Chr(40))(0)), "bar") > 0)
End Function

This function exists to test for the presence of a certain substring (bar, in this case) before the (.

The case I'm having trouble with is when the cell passed into the function is empty, the thisCell.hasFormula is false, but the statement after the and is still being evaluated. This gives me a subscript out of range error in runtime.

Does VBA actually continue evaluating the second argument to the And, even when the first was false?

Solution 1:

What you are looking for is called "short-circuit evaluation".

VBA doesn't have it.

You can see an approach that is probably adaptable to your situation here.

The approach that was chosen there involved substituting a Select Case for the If. There is also an example of using nested Ifs.

Solution 2:

As DOK mentioned: No, VBA does not have short-circuit evaluation.

It's technically more efficient to use 2 If-then statements instead of using the AND operator, but unless you are doing it a lot of times, you wouldn't notice the savings, so go for whatever is more readable. And if you want to get really technical, VBA handles multiple If-then statements faster than Select Case ones as well.

VBA is quirky :)

Solution 3:

The answer is yes, VBA does not short circuit evaluation.

It's not just a matter of style; it makes a big difference in a situation like this:

If i <= UBound(Arr, 1) And j <= UBound(Arr, 2) And Arr(i, 1) <= UBound(Arr2, 1) Then
    Arr2(Arr(i, 1), j) = Arr(i, j)
End If

...which is incorrect. More appropriately:

If i <= UBound(Arr, 1) And j <= UBound(Arr, 2) Then
    If Arr(i, 1) <= UBound(Arr2, 1) Then
        Arr2(Arr(i, 1), j) = Arr(i, j)
    End If
End If

Or if you have an aversion to nested ifs:

If i > UBound(Arr, 1) Or j > UBound(Arr, 2) Then
    ' Do Nothing
ElseIf Arr(i, 1) > UBound(Arr2, 1) Then
    ' Do Nothing
Else
    Arr2(Arr(i, 1), j) = Arr(i, j)
End If

Solution 4:

VBA does have one short-circuit-like behavior. Normally Null propagates through expressions, eg. 3 + Null is Null, and True And Null is Null. However:

? False And Null
False

This looks like short-circuit behavior - what's going on? Null doesn't propagate when the other argument to a conjunction (And) is False or 0 - the result is just False or 0. It doesn't matter if it is the left or right argument. The same applies if the other argument to a disjunction (Or) is True or a non-zero integer (a floating point value will be rounded to an integer using this rule).

So side-effects and errors can't be prevented in arguments to And and Or, but Null propagation can be "short-circuited". This behavior seems to be inherited from SQL.