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

Question

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?

Answer 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.

Answer 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 :)

Answer 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

Answer 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.

Answer 5

Since the answer is one of the top ranked in Google just looking for something like vba if condition not lazy I would like to provide a simpler example, the problem and solutions of both conditions: AND and the more interesting OR ...

Dim cond1 As Boolean   'some 1st condition that may be True or False
Dim obj As Collection  'just some sample object that may or may not be instantiated

(²: I find it better to explain other devs, why you did not choose OR if they don't know the background)

---

the AND case

cond1 = False
If cond1 Then Set obj = New Collection

problem:

If cond1 And obj.Count > 0 Then Debug.Print "Count > 0!"  'throws error if < cond1 = False > 
                                                          'because condition 2 is always evaluated

solution:

If cond1 Then If obj.Count > 0 Then Debug.Print "Count > 0!"  'AND would not short-cicuit!² https://stackoverflow.com/a/57521572/1915920

Depending on taste, complexity and readability it may make sense to write it this way:

If cond1 Then
    If obj.Count > 0 Then  'AND would not short-cicuit!² https://stackoverflow.com/a/57521572/1915920
        Debug.Print "Count > 0!"
    End If
End If

---

the OR case

 cond1 = True
 If Not cond1 Then Set obj = New Collection  'obj stays < Nothing > otherwise

problem:

 If cond1 Or obj.Count = 0 Then Debug.Print "no objects!"  'throws error if < cond1 = True >
                                                           'because condition 2 is always evaluated

solution 1:

in-place, non-redundant one-liner without GoTo using Select:

 Select Case True:  Case cond1, obj.Count = 0:  Debug.Print "no objects!":  End Select  'OR would not short-cicuit!² https://stackoverflow.com/a/57521572/1915920

in case it should/must be on multiple lines and with some else:

 Select Case True
     Case cond1, obj.Count = 0  'OR would not short-cicuit!² https://stackoverflow.com/a/57521572/1915920
         Debug.Print "no objects!"
     Case Else
         Debug.Print "object count: " & obj.Count
 End Select

solution 2:

in-place, non-redundant code with minimal GoTo usage, but more lengthy If-multi-line code:

 If cond1 Then
 noObjs:
     Debug.Print "no objects!"
 ElseIf obj.Count = 0 Then  'OR would not short-cicuit!² https://stackoverflow.com/a/57521572/1915920
     GoTo noObjs
 End If

solution 3:

in-place, conditions (may fit) on one line similar to OR-concatenation with quite some GoTo usage:

 If cond1 Then GoTo noObjs ElseIf obj.Count = 0 Then GoTo noObjs  'OR would not short-cicuit!² https://stackoverflow.com/a/57521572/1915920
 GoTo skipOnAllFalse
 noObjs:
     Debug.Print "no objects!"

 skipOnAllFalse:    'use more specific label/scenario name if possible

solution 4:

out-of-place code (Sub), avoiding GoTo, conditions (may fit) on one line, but module/class code may be more unreadable/spread/cluttered:

 Private Sub noObjs():  Debug.Print "no objects!"

 If cond1 Then noObjs ElseIf obj.Count = 0 Then noObjs  'OR would not short-cicuit!² https://stackoverflow.com/a/57521572/1915920

solution 5:

using one condition variable:

 Dim any As Boolean:  any = cond1
 If Not any Then any = obj.Count = 0  'OR would not short-cicuit!² https://stackoverflow.com/a/57521572/1915920
 If any Then Debug.Print "no objects!"

solution 6:

using multiple condition variables:

 Dim c1 As Boolean:  Dim c2 As Boolean
 c1 = cond1
 If Not c1 Then c2 = obj.Count = 0  'OR would not short-cicuit!² https://stackoverflow.com/a/57521572/1915920
 If c1 Or c2 Then Debug.Print "no objects!"  'safe to use Or now

Answer 6

I think this is the best practice:

sub my conditions()
        If Condition1=constraint1 then
         if Condition2=constraint2 then
          if condition3=constraint3 then
           ...
            ....
        end if
         end if
          end if
    else
      end if
           ....
    end if
end sub

Thus you will be only passaing through conditions if and only if condition i is fullfilled.

Answer 7

Consider the machine code that has to run. The fastest should be along the lines of a mix of code like...

if sfsf then goto SkipAB

if fdf then goto goneBad

if dffdefedwf then goto MustHave

SkipAB: if dsda > 4 then MustHave

GoneBad: exit function

MustHave: ThisIS = true

' only saves a few moments when the program has to run through it many thousands of times ... eg file searching a large drive or when a simple Boolean test is used to skip a time consuming function like finding all the sheets and names in a closed worksheet [code]

     If Not wFF.UsingFileExtMatch Then GoTo SkipExt
                If Not wFF.OKFileEXTMatch Then GoTo BADFile

SkipExt: If Not wFF.UsingFileNameMatch Then GoTo SkipFileMatch If Not wFF.OKFileNameMatch Then GoTo BADFile SkipFileMatch: If Not wFF.UsingDaysAgo Then GoTo SkipDaysAgo If Not wFF.OKDaysAgo Then GoTo BADFile SkipDaysAgo:

[/code]