Beware of clever workarounds for missing language features. Sometimes the simple approach is best.
If you have written any code in VB.Net, you may be familiar with the short-circuit boolean operators AndAlso and OrElse.
Short-circuit boolean evaluation
In order for an AND statement to evaluate to True, both expressions must evaluate to True. Thus, if the first expression is False, there is no need to evaluate the second expression; the entire statement will be False based solely on the value of the first expression. The same goes for OR statements. If the first expression is True, there is no need to evaluate the second expression; the entire statement will always be True.
Short-circuit boolean evaluation takes advantage of these properties of the AND and OR operations to evaluate only those expressions necessary to return a result. In other words, the second expression of an AND statement is only evaluated if the first expression is True. Likewise, the second expression of an OR statement is only evaluated if the first expression is False.
VBA does not use short-circuit boolean evaluation
Or statements always evaluate both expressions of the statement. Thus, code such as the following will throw an error in VBA:
As will this:
A workaround for VBA?
There's a question about this topic on StackOverflow: AndAlso/OrElse in VBA. The top-rated answer offers the following convoluted hack using a Select..Case statement:
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
I first came across this technique in an article by Ben Clothier at Access Experts. My first reaction was, "Wow, that's really clever!" I decided I should write an article sharing the technique.
I've been thinking about the article and the technique all day. (I should point out that Ben's article is not about this technique per se; this workaround is more of an aside in an article about a completely different topic.)
A cure worse than the disease?
After spending some time thinking about it, my current reaction to this Select..Case approach is, "Ugh, that's really clever."
Everyone knows that debugging is twice as hard as writing a program in the first place. So if you're as clever as you can be when you write it, how will you ever debug it?
-Brian Kernighan, "The Elements of Programming Style, Second Edition"
If I were to use the above code in my own programs, I would insist on including a comment explaining why it's being used. Something like this:
'This case statement provides short-circuit OR evaluation; ' see: https://stackoverflow.com/a/3245183/154439
Writing unnecessarily clever code is like telling a bad joke; it's not any good if you have to explain it.
Why do I insist on including the above code comment? Because, without it, I know some unsuspecting programmer will come along and "fix" this convoluted conditional statement by turning it back into the buggy OR statement whence it came:
If (myObject Is Nothing) Or Not myObject.test() Then MsgBox "no instance or test == false" Else MsgBox "got instance & test == true" End If
The above code will fail when myObject is Nothing, but a programmer unfamiliar with this "hidden feature" of VBA will be none the wiser. How am I so sure about this? Because I was once that unsuspecting programmer with a different, but similarly clever, VBA programming technique.
Don't be clever, be clear
I know it feels wrong. It goes against every programmer instinct in your body. But please resist the urge to be clever. How would I rewrite the above code? I would create a well-named flag variable and assign it a value via a series of If..ElseIf statements. Something like this:
Dim IsNothingOrTestFails As Boolean If (myObject Is Nothing) Then IsNothingOrTestFails = True ElseIf Not myObject.test() Then IsNothingOrTestFails = True End If If IsNothingOrTestFails Then MsgBox "no instance or test == false" Else MsgBox "got instance & test == true" End If
Depending on the situation, I might be able to make it even more readable using the "guard clause" approach. It's often possible to split the relevant section of code out into its own function. This would be my preference; it is the most readable by far.
If (myObject Is Nothing) Then Exit Function If Not myObject.test() Then Exit Function MsgBox "got instance & test == true"
Image by Free-Photos from Pixabay