AndAlso/OrElse in VBA

Question

I'm trying to get a lazy evaluation with 'And' in my Excel macro by doing the following:

If Not myObject Is Nothing *And* myObject.test() Then
    'do something'
Else
    'do something else'
End If

I know lazy evaluation exists in VB.NET as AndAlso and OrElse but cannot find anything similar in VBA. If lazy evaluation does not exist in VBA, what's the best way to structure the code so that it will evaluate the way I expect?

Answer 1

The only short circuiting (of a sort) is within Case expression evaluation, so the following ungainly statement does what I think you're asking;

Select Case True
    Case (myObject Is Nothing), Not myObject.test()
        MsgBox "no instance or test == false"
    Case Else
        MsgBox "got instance & test == true"
    End Select
End Sub

Answer 2

This is an old question, but this issue is still alive and well. One workaround I've used:

Dim success As Boolean       ' False by default.

If myObj Is Nothing Then     ' Object is nothing, success = False already, do nothing.
ElseIf Not myObj.test() Then ' Test failed, success = False already, do nothing.
Else: success = True         ' Object is not nothing and test passed.
End If

If success Then
    ' Do stuff...
Else
    ' Do other stuff...
End If

This basically inverts the logic in the original question, but you get the same result. I think it's a cleaner solution than the others here that only use If statements. The solution using a Select statement is clever, but if you want an alternative using only If statements, I think this is the one to use.

Answer 3

Or you could create a function that takes your object as a parameter and returns boolean for either case. That's what I usually to.

i.e.

if Proceed(objMyAwesomeObject) then
       'do some really neat stuff here
else
       'do something else, eh
end if
...
end sub

private function Proceed(objMyAwesomeObject as Object)
     if not objMyAweseomeObject is nothing then
            Proceed = true
     elseif objMyAwesomeObject.SomeProperty = SomeValue then
            Proceed = true
     else
            Proceed = false
     endif
end function

Answer 4

Improving on this answer to a different question about the same basic problem, here is what I chose to do:

dim conditionsValid as boolean

conditionsValid = Not myObject Is Nothing
if conditionsValid then conditionsValid = myObject.test()
if conditionsValid then conditionsValid = myObject.anotherTest() 

if conditionsValid then
   'do something'
else
   'do something else'
end if

I think this code is clearer than the other answers that have been suggested, and you (usually) don't need a different variable for each validation, which is the improvement over the original answer to the other question. By the way, each new condition you need adds just one more line of code.

Answer 5

Since the following syntax works

If myObject.test() Then do something

Then the one liner syntax could be used to short circuit the evaluation. Below, the first If statement ensures that myObject is something. Otherwise, it won't even try to evaluate the second If.

If Not myObject Is Nothing Then If myObject.test() Then
    'do something'
Else
    'do something else'
End If

Of course, if you want 'do something else' if myObject Is Nothing, then this may not work.

---

Update 2020/06/30

After a comment pointed out that this answer didn't work, I have verified that the syntax does not appear to work in modern VBA. Leaving original answer for legacy purposes.

Answer 6

If Not myObject Is Nothing Then
    If myObject.test() Then
        'do something'
    End If
Else
   'do something else'
End If

I think that's the way you have to do it.

Edit

Maybe like this

Dim bTestsFailed as Boolean
bTestsFailed = False

If Not myObject Is Nothing Then
    If myObject.test() Then
        'do something'
    Else
        bTestsFailed = True
    End If
Else
   bTestsFailed = True
End If

If bTestsFailed Then
    'do something else
End If

Isn't VBA great?

Answer 7

A trick around missing values may help:

Dim passed, wrongMaxPercent, wrongPercent, rightMinPercent, rightPercent
wrongPercent = 33
rightPercent = 55

'rightMinPercent = 56
wrongMaxPercent = 40

passed = (Len(wrongMaxPercent) = 0 Or wrongPercent < wrongMaxPercent) And _
         (Len(rightMinPercent) = 0 Or rightPercent >= rightMinPercent)

Answer 8

One can switch the logical condition, working with the Or operator and switch off error messages like this:

Err.Clear
On Error Resume Next
If myObject Is Nothing Or Not myObject.test() Then
    Err.Clear
    'do something else'
Else
    'do something'
End If
On Error Goto 0 ' or On Error Goto ErrorHandler (depending on previous setting in the code)

The test for Nothing is not necessary - it only serves to clarify what is meant.

Answer 9

I don't know any equivalent for OrElse, but there is a limited but useful solution for AndAlso. The following two are equivalent:

• vb.net: If Condition1 AndAlso Condition2 Then DoSomething
• vba: If Condition1 Then If Condition2 Then DoSomething

The are two limitations:

1. It only works as a one liner, cannot be used to start an if-block
2. The Else block is executed if the first condition is true and the second is false, not when the first condition is false

Even considering these two fairly crippling limitations, I often use this little trick when I don't have an Else block.

Here is an example:

Sub Test()
  Dim C As Collection

  ' This is what I often use
  If Not C Is Nothing Then If C.Count Then DoSomethingWith C

  ' Here are other usages I stay away from, because of bad readability
  If Not C Is Nothing Then If C.Count Then Debug.Print "not empty" Else Debug.Print "empty or nothing"
  Set C = New Collection
  If Not C Is Nothing Then If C.Count Then Debug.Print "not empty" Else Debug.Print "empty or nothing"
  C.Add 1
  If Not C Is Nothing Then If C.Count Then Debug.Print "not empty" Else Debug.Print "empty or nothing"
End Sub```

Answer 10

We might want to say: If XX < 7 Then XX = 19 but XX might be Null, so we have to test.

We can do this: If Switch ( IsNull( XX ) , True, True, XX < 7 ) Then XX = 19 So, if XX is Null, we drop through to do the assignment, otherwise only do it if the test, XX < 7, is True.