IIf Function

[Ken Hudson]

I just ran across the IIf function and am trying to clarify its usage.
I would normally write:

If Len(Range("A1")) > 0 then
Var1 = "Yes"
Else
Var1 = "No"
End If

It looks like I could write:

Var1 = IIf(Len(Range("A1")) > 0, "Yes" , "No")

Is my logic correct?
Would this be the principle use of that function, to shorten the coding
required in my example? Or is this function used in some other types of cases?

 

[Mike H]

Hi,

In your simple example both snippets evaluate the same but consider this
equally simplistic example

Var1 = IIf(Len(Range("A1")) > 0, "Yes", 22 / 0)

Even if this evaluates as TRUE (Len(A1)>0) you will still get an error
(Div/zero) because in IIF both parts are evaluated unlike an If/then
statement where evaluation stops as soon as a condition satisfies the
statement.

If Len(Range("A1")) > 0 Then
Var1 = "Yes"
Else
Var1 = 22 / 0
End If

Would work if A1>0

Mike

 

[Ken Hudson]

Thanks Mike.
I think that I read that somewhere.
Is there a clear example then of when one would need the IIf function?

 

[Mike H]

Hi,

For the reason given I avoid it's use and have never found any compelling
reason to change that view. In addition the extra call in evaluating the
false part (assuming it has evaluated as true) could be an issue if speed and
preformance are a factor in your code.

Mike

 

[Bob Phillips]

To add to Mike's thoughts, there is never a situation where you would need
Iif because If ... Else ... Endif can always do the same job. I use it very
occasionally where I think it makes the code read more clearly.

 

[Rick Rothstein \(MVP - VB\)]

The IIF function is much slower than using an If..Else..Then construction,
so it is probably a good rule to never use it in a large loop. The only time
I tend to use it is where the true and false arguments are constants. For
example,

Cell.Interior.IndexColor = IIF(<<logical expression>>, 3, 5)

Restricting yourself to constants only avoids the time wasted evaluating an
expression that won't be used (one of the two expressions is guaranteed not
to be used) and restricting yourself to single statements or, at worse, very
small loops minimizes the speed disadvantage as compared to using
If..Else..Then. By the way, more than likely, I would code the above line
like this...

Cell.Interior.IndexColor = 5 - 2 * (<<logical expression>>)

which would match the speed of the If..Else..Then construction while giving
me the advantage of an inline function call (especially useful in a chained
calculation).

Rick