Why Use IIF (general)

Topic

New Reply

I have just today seen the ‘IIF” statement, by virtue of a client’s question to me about Access VBA code.

I tried it out in Word97SR2 and saw what it did.

I’ve read the thread about “solely in Access” and “no it’s not”.

If you currently make use of the IIF, what prompted you to prefer that over the regular nestable If-Then-Else?

I’m struggling to find a reason for ever using IIF in preference to the regular nested version.

I’m particularly interested in examples where one can do with IIF what can’t be done (!) using If-Then-Else.

Reply | Quote

Replies

There would be no reason to use IIF (Immediate If) in normal VBA coding in preference to the IF Then constract, unless it is part of a query string where the usual IF approach would not work. Although it is included in all versions of VBA, it has little use outside of Access.

Consider the following

Sub IIFProblem()
int1 = 0
int2 = 2
MsgBox IIf(int1 = 0, “”, int2 / int1)
End Sub

This will result in a divide by zero error, as all arguments are evaluated, even though in this case the last is irrelevant.

So handle with care and only if you need to.

Andrew

Reply | Quote

IIF is a shortcut. It can help to avoid temporary variables.

strSQL = “SELECT * FROM EMPLYOYEES WHERE EMPLOYEE_ID ” & IIF(strID = “”, “IS NULL”,” = ” & strID)

Reply | Quote

> This will result in a divide by zero error, as all arguments are evaluated,

Right. It’s the same argument as that for couching some conditions in nested IF statements rather than employing AND technology.

Reply | Quote

I don’t think that I have ever used the iif. To me it seems to obfuscate the code. But, check out the next to last “tip” at http://msdn.microsoft.com/library/default…._tentipsvba.asp[/url]. Click on the “Use the IIf Function for Simple True-False Comparisons.” Can Microsoft be wrong?

Reply | Quote

In C# (and other C-based languages??) it’s called the Ternary Operator and it looks like this:

expression ? true-part : false-part

so iif(x=1,”True”,”False”) becomes

x=1?”true”:”false”

Ain’t that sweet? I have had affectations for immediate if and now have the same for ?:

Maybe a bit cryptic, but it’s really a lovely construct if for no other reason
than it is succinct. Some say elegant.

Reply | Quote

> Ain’t that sweet?
That is plug-ugly! It could only appeal to a Unix programmer! Might be good job security, though. Maybe we should switch back to RPG. Object-oriented RPG, I love it, let’s call it RP++. –Sam

Reply | Quote

I knew I’d get your goat on that one…

Ever heard of APL? Now that’s cryptic.

Ternary operators unite!

Reply | Quote

If I remember (and that’s doubtful), APL is the C5-A (that’s a monster carge plane) of programming languages. They got the design-specs for both of them identically: they took a survey of all the pilots/programmers in the world and asked them what they wanted to have in an airplane/language. They then took the survey and repackaged it as the design specs!

I worked on C5’s for a time and they had 5 or so navigation systems. Someone (see above) had the “bright(?)” idea to create a system to monitor all of these nav-systems and do an automatic maintenance report on any system that didn’t agree with the others. A good idea? NOT! Whenever you got one of those reports, guess what you did? You replaced the system that was comparing all of the nav-systems. Nav systems have to be reliable; the write-up system did not. I think that the same holds true in programming: new is not usually better, cryptic is never better.

With that, I will step-down from my soap-box and probably trip.

Reply | Quote

Sammy, baby, you need a day off…

Reply | Quote

Can’t, I’ve got a C5 to get flying in Excel. It takes multi-column CSV files, rearranges the columns into separate sheets (col 1 is the independent variable, rest of the columns are sets of dependent variables. Then it plots cross-sections of these sheets at various user-selected rows.

Reply | Quote

You don’t remember much about APL. The original concept was the work of one man, Kenneth Iverson. The first implementation as a computer language was the work of about 6. AFAIK, there was no survey. Unfortunately for APL, it came about 30 years before Unicode, and the need for special keyboards and display facilities was probably the thing that held it back the most. I still remember changing a Hercules card chip when installing an early PC version to get the characters I knew and loved. Enough nostalgia already. Ironically, an ASCII-only version called J came out just about the same time as Unicode was being devised.

If you can deal with the idea of a ternary operator, then Iif makes a lot of sense – one line of code instead of 5, and a better chance of seeing all your program without scrolling (always a winner for APL code!).

Reply | Quote

> APL? Now that’s cryptic.

Hmmm. I have a copy of Kenneth Iverson’s book in front of me. Chapter 2, in which he describes each part of the IBM 7090 in APL is a masterpiece. The complete instruction fetch IN MICROCODE is expressed in only 18 lines of APL. The bubble-sort is but 10 lines.

“Elegant, Concise” are words that come to mind.

Reply | Quote

I didn’t say it wasn’t a nice compact way to code. But those symbols, oye vey!

“Famous for its enormous character set, and for being able to write whole accounting packages or air traffic control systems with a few incomprehensible[/b] key strokes.”
http://public.logica.com/~stepneys/cyc/p/prog.htm%5B/url%5D

Reply | Quote

Wow! That reminds me a lot of my logic course at uni. The little I remember of it anyway….

Reply | Quote

Now don’t forget, if you decide to start writing Office apps with APL, you’ll
need the .Net CLS compliant compiler and ONE OF THESE BABIES:

Reply | Quote

> it’s called the Ternary Operator

Rinse your mouth!

When I was your age (VBG!) we used “FORTRAN II” and used a 3-way IF statement. I look back at those days in horror, but it was all we had,…..

If (JDIM-30)10,20,30

where the 10, 20 and 30 represented statement numbers to which we should GoTo.

Reply | Quote

> Can Microsoft be wrong?

Ask not “Can Microsoft be wrong?” but “Could they have picked a poorer example of use?”

Reply | Quote

> Ask not “Can Microsoft be wrong?” but “Could they have picked a poorer example of use?”
I could not have put it better myself! It was worse than their classic poor examples!

> we used “FORTRAN II
Ah, the good old days. When I retire, I’m going to write an object-oriented FORTRAN++ compiler and integrate it with OpenOffice Excel.

Reply | Quote

IIF is slower than using the normal IF, and has the shortcomings others are pointing out.

Reply | Quote

Howard,

I am aware of this but as you had aleady made that point here, and as Chris had provided a link to that thread I assumed he had taken that on board.

Andrew C

Reply | Quote

A few things. First of all, IIF is nestable. It’s nasty, and a real pain to troubleshoot, but it is certainly nestable. (I personally don’t use it myself except in really rare cases where I need it in a query).

Also, there is an actual difference between an IIF statement and an If then Else statement. The difference is that with an If Then Else statement, only the If statement and the ‘result’ are checked. So if you have:

If X0 then
X=5/X
Else
X=1
End if

In the code above, you are checking to make sure you don’t divide by 0. If you were to use an IIF statement for the same ‘code’, it will fail, because an IIF statement actually checks BOTH the True and False results, regardless of the ‘expression’ result. I have no idea how this can be an advantage, to me it is a liability, but it is a notable difference between the two statements.

Reply | Quote

No idea why one would use IIF.

I used IIF in my first versions of my Excel Name Manager (download from Excel MVP page below).

When I changed all of them to If-Then-Else constructs, the speed tripled!

Reply | Quote

As noted by others, IIF function can be useful in an Access query expression, otherwise If…Else statement is more efficient and readable. As example, in one somewhat convoluted Access query using several IIF expressions, I had a “brainstorm” that query execution could be sped up by replacing IIF expression with user-defined function that used If…Else statements. Both the IIF and If…Else statements were only 1 level deep, ie, not nested. The query using user-defined functions slowed to a crawl, even on a new, speedy computer. (User-defined functions in Access queries do not tend to be very efficient in general, in cases where function is executed once for every record returned by query.) So in this case it was back to the lowly, much-derided IIF function….

You VB.NET fans will be glad to that the IIF function has survived transition to VB.NET (as member of the Microsoft.VisualBasic.Interaction class). According to one reliable source (VB.NET Language in a Nutshell), the performance of IIF has improved by 100% in VB.NET compared to VB 6, but at same time is appx 300 time slower than an equivalent IF statement. Also of interest, the same reference notes that VB.NET, unlike VB 6, now supports logical short-circuiting when evaluating IF statements thru the use of the new AndAlso and OrElse logical operators. That is, subexpressions in an IF statement are evaluated from left to right, and once it is determined that the expression is true or false, the remaining subexpressions are not evaluated. Example:

If (X AndAlso Y) Then

If X is False, then Y is not evaluated, because expression as whole cannot equate to True.

Reply | Quote

> can be useful in an Access query expression,

Mark, thanks for the summary. I don’t do a great deal of Access VBA (one of my many faults!) and this may explain my late arrival on the scene.

In general, it seems, that if IIF appears in Office Suite VBA anywhere but Access, there ought to be a darn good and solid explanation behind it.

>the new AndAlso and OrElse logical operators

Interesting. We used to have to read the Reference Manual to learn whether the compiler evaluated all components prior to evaluation, or not. The safest course was to assume that all components WERE evaluated, thereby getting around the division-by-zero problem.

Now the source language has absorbed the concern by allowing the programmer, in effect, to dictate HOW they’d like the components to be evaluated. (Not really true, but it does allow the programmer to step around a potential problem). This may actually be a backward step, much like the On Error, which at times seems to be a lazy way of saying “I can’t be bothered trying to work out what might go wrong, in advance”.

Reply | Quote

Are you trying to have it both ways, Chris?

Reply | Quote

Do you have an iif embedded in your UserPic? It comes and goes and in any case is undecipherable.

Reply | Quote