AndAlso/OrElse in VBA

user_358680 0
Viewed 25319

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?

vba vb.net
9 Answers

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
user_12671580

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

user_23164330

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

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.

user_28837330

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```
user_18996280
Related