Avoid IIf(), Switch(), and Choose() in VBA code

[ADezii]

The tendency of VBA code to evaluate all expressions, whether or not they need to be evaluated from a logical standpoint, makes the use of the IIf(), Switch(), and Choose() Functions inefficient and downright dangerous. For these reasons, they should 'never' be used in VBA code. A couple of examples using IIf() will clearly illustrate this point which applies equally well to Choose() and Switch().

Example 1:

varValue = IIf(BooleanExpression, Function1(), Function2())

Both Functions will be called possibly leading to undesireable side effects, and a definate slowdown of you program execution. The If...Then...Els e construct, although longer, would be more efficient since only 1 Function will be called. Again, the same applies to both Choose() and Switch(). Here is the more efficient method:

'only 1 Function is called depending on the outcome of BooleanExpression
If BooleanExpression Then
   varValue = Function1()
Else
   varValue = Function2()
End If

Example 2:

dblNew = IIf(intY = 0, 0, intX / intY)

The above line of code would at first appear relatively harmless since it apparently covers the case where intY = 0, but this is far from True. Remember, the 2nd expression will also be evaluated and it will subsequently generate a runtime Error (Division by 0).

NOTE: I saved the best for last! These Functions when used in Queries, Forms, or Reports do not exhibit the same behavior and work exactly as you would expect. It is only in VBA code, in a Module, where you need to avoid IIf(), Switch(), and Choose().

[NeoPa]

This is an important point to make ADezii.
However, I think to avoid these quite useful functions altogether simply because they can, in certain limited circumstances, noticeably degrade the performance of a project, is possibly overkill.
These limitations are certainly worth bearing in mind, especially if the various options execute large amounts of, or particularly slow, code.

On your last point, this is very interesting and I'd be very interested in a link to allow me to investigate further. I was unaware of any such difference in the SQL versions of these functions.

[ADezii]

This is an important point to make ADezii.
However, I think to avoid these quite useful functions altogether simply because they can, in certain limited circumstances, noticeably degrade the performance of a project, is possibly overkill.
These limitations are certainly worth bearing in mind, especially if the various options execute large amounts of, or particularly slow, code.

On your last point, this is very interesting and I'd be very interested in a link to allow me to investigate further. I was unaware of any such difference in the SQL versions of these functions.

I must confess that I was a little hesitant about this particular Tip since I know that you are quite fond of these 3 Functions. Please don't take it personally, I just find the details relating to them very interesting.

NOTE: The reference source for this particular Tip is The Access 2002 Desktop Developer's Handbook by Paul Litwin, Ken Getz, and Mike Gunderloy. The specific location is Chapter 15 - Application Optimization, Test 17: Watch Out for IIf - It Doesn't Short-Circuit.

[NeoPa]

Personally, I think you were right to make the point my friend.
The emphasis may have been over-strong, but considering the target audience, it's an important point that people should, at least, be aware of.
There are certainly situations where I wouldn't dream of using them (Actually, though I was aware of the IIf() problem I didn't realise it covered Choose() & Switch() too).
People with very large databases who are looking to use the Domain Aggregate functions within one of these functions should be particularly cautious. I only use them with either very small datasets or when it is impossible to get the same functionality using a sub-query in SQL (which is very rare).

It's a shame you found it in a book as I wanted to read through it myself (easily - you know, on the web).

[Rabbit]

Tip #3 should be: How to use the help files.

[NeoPa]

Yeah, but they change so drastically for each version that would be a tome in itself :D

[Rabbit]

Yeah but some things are common like if you highlight a function and press F1 it will bring up the help file for it. Or if you don't know what a property does you can focus on the property and press F1 to bring up that help file. And how these things bring up different help files depending on if you're in Access or the Visual Basic Editor. And how they should look look at the See Also list for related topics.

[NeoPa]

Very fair point.
I was hoping to find time to do one on debugging at some stage. Please feel free to take this one on if you feel inclined. I agree that it may well be very helpful and save many explanations - if we could say RTFM (Or the help file equivalent) while pointing them at such a helpful (excuse the pun) Tutorial.

[Denburt]

NEO I thought I read about this in the helpfile so I was in my VBA just now an typed in IIF, Switch, and Choose highlighted it hit F1 and each one was in there the point ADezii is making is right near the bottom. :)

[Denburt]

OK I looked it up just for fun it is on the web on the Microsoft Site.

Developer Handbook Chapter 15