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.
The IFF function is made up of 3 parts – the logical test, the true part and the false part.
- 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
Function GetNames(strName As String) As String GetNames = IIf(strName = "John", "The name is John", "The name is not John") End Function
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”
Sub TestGetNamaes() MsgBox GetNames("John") End Sub
If we were to run the TestGetNames sub-procedure, it would call the GetNames Function and return a message box.
If we had used the If method instead, the code would have looked like this:
Function GetNames(strName As String) As String If(strName = "John") Then GetNames = "The name is John" Else GetNames = "The name is not John" End If End Function
We have effectively written one line of code instead of 5 lines of code – impressive!
Why use If Instead?
Consider the following
Function GetNames(strName As String) As String GetNames = IIf(strName = "John", MsgBox("The name is John"), MsgBox("The name is not John")) End Function
Now if you run the following Sub-Procedure to call your function
Sub TestGetNames() GetNames ("John") End Sub
You would get the same message box as before, but then immediately after that – you would get the next message box!
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.
Function GetNames(strName As String) As String GetNames = IIf(strName = "John", "The name is John", "The name is not John") MsgBox (GetNames) End Function
If you are careful when you write you code, the IIF function can save you plenty of lines of code and extra typing!
We can nest the IIF function in a similar fashion to nesting the IF function but once again, everything is done in one line.
Function GetDiscount(dblPrice As Double) As Double GetDiscount = IIf(dblPrice >= 500, 10, IIf(dblPrice >= 250, 5, IIf(dblPrice >= 100, 2.5, 0))) End Function
We could then call this function from a Sub-Procedure
Sub FindDiscount() Dim dblP As Double dblP = 899 MsgBox ("The discount you can obtain is " & GetDiscount(dblP) & "%") End Sub
or you could call it from within Excel, using it as a UDF (User Defined Function)
VBA Coding Made EasyStop 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!