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?

Note that VBA's `And` operator doesn't short circuit because it is a *bitwise* operator and not a *logical* one. See: http://stackoverflow.com/questions/8042744/excel-vba-instr-condition-doesnt-work-with-2-or-more-conditions/8047021#8047021 — jtolle, May 12 '13 at 16:50
@jtolle not true - it will return a Boolean if its arguments are Booleans, so it supports both bitwise and logical operation. (sure you could argue that logical is a special case of bitwise using 1-bit integers, but the point is that Microsoft could have been supported short-circuiting if they chose to) — Hugh Allen, May 22 '14 at 13:08
@Hugh, interesting. All this time I've been assuming that 'And' was only a bitwise operator, albeit one that simulated logical operations because 'True = -1' and 'False = 0'. But you're right that 'And' *is* a logical operator if both expressions passed to it are Boolean. It's only bitwise if one or both operands are numbers. But I guess it can't short-circuit because both expressions must be evaluated anyway, in order to ensure that one or both aren't numbers and not booleans. So I think that "bitwiseness" does still lead to no short-circuiting here. — jtolle, May 23 '14 at 17:12
@jtolle Another thing that VBA supports is strong typing. Using Variants is optional. If the arguments to a logical operator are known to be Boolean at compile time, then yes it could still support short-circuiting. Even with integers, the right argument could be skipped if the left argument to `Or` was "all 1s" (`&HFFFFFFFF`, or `-1&`), and likewise for `And` if the left argument was 0. — Hugh Allen, May 24 '14 at 03:06
@Hugh, That's all certainly true. It might be kind of confusing though. I'm not aware of any short-circuiting bitwise operators in other languages. Also, VBA was presumably trying to maintain backwards compatibility with older versions of BASIC. Better to add new operators, as MS eventually did for VB.NET. (The BASIC ancestry of VBA shows up in other places too, for example one of my favorites: http://stackoverflow.com/questions/1070863/hidden-features-of-vba/5554208#5554208) — jtolle, May 25 '14 at 19:07

score 64 · Accepted Answer · edited May 23 '17 at 12:02

64

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.

edited May 23 '17 at 12:02

Community

1
1

answered Aug 10 '11 at 18:03

DOK

32,337
7
60
92

81

When I'm working with VBA it starts to feel like I'm trying to draw the Mona Lisa with crayons. – James Aug 10 '11 at 18:11
1

Indeed. Too bad that you can't use C# instead :( – Kapé Sep 26 '15 at 14:04

score 12 · Answer 2 · edited May 24 '17 at 00:44

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

score 2 · Answer 3 · answered May 10 '13 at 23:26

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

score 2 · Answer 4 · answered May 22 '14 at 12:49

2

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.

answered May 22 '14 at 12:49

Hugh Allen

6,509
1
34
44

Hmm, I'm lost, could you add an example of how it works for a condition `A And B` – Enissay Aug 15 '16 at 11:16
@Enissay two of my examples use `And`. Perhaps you could better explain what you are confused about? – Hugh Allen Aug 15 '16 at 16:13
2

This has nothing to do with short circuit behavior. The two expressions are evaluated, the first returns `False` and the second returns `True`, there is no short circuit here. Then VBA does some weird casting and inconsistently returns either `False` or `Null` – stenci Jun 22 '17 at 17:41

Andreas Covidiot · Answer 5 · 2019-08-16T10:01:18.627

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

score 0 · Answer 6 · answered Jan 06 '17 at 23:22

0

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.

answered Jan 06 '17 at 23:22

Moreno

608
1
9
24

Over 5 years ago, the OP was asking if the `and` keyword in a conditional statement 'short circuited' if first condition was false. Are you answering that question? – ppovoski Jan 06 '17 at 23:48
@ppovoski yep I really am – Moreno Jul 17 '17 at 02:39

score -1 · Answer 7 · answered Nov 30 '16 at 07:06

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]

I think this is just a version of other answers substituting nested `If`s with `GoTo`s. Since I would consider using `GoTo`s in such simple cases as bad programming style this answer is a little out of scope. — Andreas Covidiot, Aug 16 '19 at 07:07

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

7 Answers7

the `AND` case

the `OR` case

Linked

Related

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

7 Answers7

the AND case

the OR case

Linked

Related

the `AND` case

the `OR` case