Demystifying the Use of the IIf Function in VBA

The IIf function (short for Immediate If) allows one of two choices to be selected based on whether a test value is True or False. It looks like this:

MsgBox "Right now I am " & IIf(Happy(Now), "happy", "sad")

If the function Happy(Now) evaluates to True, you will see a message box that says “Right now I am happy”, and if it evaluates to False, you will see a message box that says “Right now I am sad” (which assumes the opposite of happy is sad, which is a matter for another article).

You never need to use the IIf function, but it can provide a shorthand in certain cases.

A Good Illustration

Let’s take another scenario. Let’s suppose a sales representative receives a commission of 10% normally, but receives 15% if her sales are above $2000. We could write:

If Sales > 2000 Then
    Commission = Sales * 0.15
Else
    Commission = Sales * 0.10
End If

However, using IIf we could write the single line:

Commission = IIf(Sales > 2000, Sales * 0.15, Sales * 0.10)

You could also write the following equivalent statement:

Commission = Sales * IIf(Sales > 2000, 0.15, 0.10)

The basic syntax is:

IIf(test_expression, value_if_test_is_true, value_if_test_is_false)

Remember it’s a function, so you generally assign its value to a variable or use it in an expression, as in the commission examples above.

More Technical Details and a Warning

While the above two statements seem virtually identical, he second method is actually slightly more efficient in this case, and potentially significantly better as a general example. The reason it’s better is because the IIf function evaluates both the If-True and the If-False expressions, regardless of whether the test value is True or False. Therefore, in the first scenario Sales * 0.15 and Sales * 0.10 will both be calculated, but only one will be selected as the value of the IIf expression. That’s why the second statement is actually slightly better than the first.

This can become extremely important in certain more complicated cases, especially when the If-True part or the If-False part includes a function call itself.

A More Complex Example

For example, suppose we have a restaurant and we have code to prepare the menu. The daily “special” is usually steak, except for salmon on Fridays (religious observance). We have a function that provides the complete text of the special called Specials. You provide the name of the dish, and it gives back the complete text of the special:

Function Specials(ByVal Dish As String) As String
    If Dish = "Salmon" Then
        Specials = "Today's special is the Grilled Salmon with saffron rice."
    ElseIf Dish = "Steak" Then
        Specials = "Today's special is the Strip Steak with potatoes."
    Else
        Specials = "There is no special today."
    End If
End Function 

Now suppose we have a statement that retrieves the full text of the special:

SpecialText = IIf(WeekDay(Now) = 6, Specials("Salmon"), Specials("Steak"))

The problem with this statement is that the Specials function is called twice every time, and the entire Specials function is executed twice. This can be a serious issue, not only due to performance degradation, but also because these secondary functions may have unintended and undesired side effects.

In the specific example above, a more efficient way to write the code is:

SpecialText = Specials(IIf(WeekDay(Now) = 6, "Salmon", "Steak")

By the way, WeekDay and Now are standard VBA function you may find useful. To learn more about either, type one of them (“WeekDay” or “Now”) into a code window and press F1 to see the help screen.

More Simple Examples

Here are a couple more rather silly examples for further illustration:

If we have two children, and the one named Ryan is 16 and the other is 19, then the following statement gives the age of the given child:

Age = IIf(Name = "Ryan", 16, 19)

If we have several cars, and one is a Chevy Malibu and the others are all Fords, then the following statement gives the make of the given car:

Make = IIf(Model = "Malibu", "Chevy", "Ford") 

You can also nest the IIf's, but remember the warnings provided above. Suppose we have a Chevy Malibu and an Acura Legend and the rest of our (seventy-two) cars are all Fords.

Make = IIf(Model = "Malibu", "Chevy", IIf(Model = "Legend", "Acura", "Ford") 

The Bottom Line

The bottom line: Keep the If-True and If-False parts of the function as simple as possible and try to avoid embedded function calls.

MsgBox IIf(ReaderWillNowUseIIf, "Yay!", "Oh no!")