Does the VBA "And" operator evaluate the second argument when the first is false?
ExcelVbaOperatorsExcel Problem Overview
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?
Excel Solutions
Solution 1 - Excel
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 - Excel
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 - Excel
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 - Excel
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.
Solution 5 - Excel
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
Solution 6 - Excel
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.
Solution 7 - Excel
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]