Return to VBA Code Examples

VBA IIF Function

This tutorial will explain the how to use IIF in VBA

The VBA IIF function is similar to using the IF Function in Excel. It tests if a condition is met, returning one value (or calculation) if TRUE, another value (or calculation) if FALSE.

It is similar to the VBA If Statement, many consider it a shortcut to using this method as you only need to write one line of code to get the desired result rather than using an If..Then..Else..End If routine. However, it needs to be used correctly as it can lead to problems in your code.

IIF Syntax

The IFF function is made up of 3 parts – the logical test, the true part and the false part.

vba iif syntax

  • Expression: The logical test that is to take place.
  • True Part: The result that will be returned if the logical test is TRUE.
  • False Part: The result that will be returned if the logical test is FALSE.

Writing an IIF Function Procedure

In the function above, we can use a sub-procedure to test to see if the variable we pass to the function is the string “John”

If we were to run the TestGetNames sub-procedure, it would call the GetNames Function and return a message box.

vba iif example

 

If we had used the If method instead, the code would have looked like this:

We have effectively written one line of code instead of 5 lines of code –  impressive!

Why use If Instead?

Consider the following

Now if you run the following Sub-Procedure to call your function

You would get the same message box as before, but then immediately after that – you would get the next message box!

vba-iif-not-example

The IIF function executes both the TRUE and the FALSE sections of the line of code – it does not exit the code once it has found the condition to be true – it still executes the false section as well – thus giving us a false message in the second message box.  If you had used  If..Then..Else..End If – this would not have occurred – the IF function only executes either the TRUE or the FALSE section of the code – depending on the logic being passed to the code.

The code is badly designed (on purpose!) with the message boxes being held within the line of code that the IIF statement is on rather than after the code, or  in the sub-routine.  As the IIF function runs both the TRUE and the FALSE sections of the statement, both messages are returned.

We could fix this error by moving the message box to below the IIF function line, as in the code below, or by moving the message box  to the Sub-Procedure as per the first example in this article.

If you are careful when you write you code, the IIF function can save you plenty of lines of code and extra typing!

Nested IIF’s

We can nest the IIF function in a similar fashion to nesting the IF function but once again, everything is done in one line.

We could then call this function from a Sub-Procedure

or you could call it from within Excel, using it as a UDF (User Defined Function)

vba iif example excel

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users! vba save as


Learn More!