VBA IIF

What is Excel VBA IIF?

The Excel VBA IIF Function enables the user to evaluate a value or an expression based on the true and false conditions set and get either of them as a result. Although VBA IIF works similar to the Excel IF Function and VBA Statement, one needs to be careful in its execution to get the desired result as VBA involves coding.

What Does VBA IIF Condition Do in VBA?

IIF in VBA is a replica of the worksheet function IF. If you have already mastered the excel function IF; understanding the VBA IIF is much simpler. Though it is similar to the IF function, the difference is that VBA IIF evaluates both the true and false parts, but it returns a single result.

To understand it better, let’s look at a simple example using VBA IIF.

Step 1: Begin a sub-routine by naming a macro.

Step 2: Define a variable with the Integer data type to assign an integer value to it.

Step 3: Define another variable as Integer to assign the second value to it.

Step 4: Next, add the VBA IIF function, apply the logical expression as shown below, and assign its value to the variable “Result.”

Step 5: Assign integer values to variables V1 and V2.

Step 6: Next, add the VBA IIF function, apply the logical expression as shown below and assign its value to the variable “Result.”

Step 7: Let’s display the output of the variable Result in a message box. The complete code is below for your reference.

Sub IFF_Intro()
Dim V1 As Integer
Dim V2 As Integer
Dim Result As String
V1 = 50
V2 = 35
Result = IIf(V1 > V2, “A”, “B”)
MsgBox Result
End Sub

Run the code and see the following result in a message box.

The value returned by the IIF condition is “A”.

Explanation:

We have applied the logical test as follows:

V1 > V2

It will check if the value of the variable V1 is greater than that of the variable V2. The VBA IIF will return “A” if the test result is TRUE. Else, it will return “B.”

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

VBA IIF Syntax

Now let’s look at the syntax of the VBA IIF function.

IIF (Expression, True Part, False Part)

• Expression: In this argument, we will apply the logical condition to test.
• True Part: When the above Expression evaluates to true, this value is returned.
• False Part: When the Expression evaluates to false, this value is returned.

Examples

This section will show you some practical examples of implementing the VBA IIF statement.

Example #1: VBA IIF

Let’s look at a basic example using the VBA IIF function. First, take a look at the following data in Excel.

We have a target value in cell A2 and the achieved value in cell B2. In cell C2, we must calculate the incentive percentage using the following logical test.

• If the achieved value in cell B2 is greater than the target value in cell A2, the incentive percentage should be 10%; else, it is 5%.

Follow the steps listed below to apply the VBA IIF statement.

Step 1: Start the sub-procedure by naming the macro.

Step 2: The result of the VBA IIF condition will go to cell C2; hence, apply the C2 cell reference as follows.

Step 3: Enter the VBA IIF function after the equal to = sign.

Step 4: The first argument of the VBA IIF function is expression, i.e., the logical test to be evaluated. In this case, we must evaluate if the achieved value in cell B2 is greater than the target value in cell A2.

Apply the logical test shown below.

Step 5: The next argument is the true part, i.e., if the applied logical test in the expression argument is true, this is evaluated. If the applied logical test is TRUE, the incentive is 10%, written in cell C2.

In this case, if the applied logical test is TRUE, then the incentive is at 10%, written in cell C2.

Step 6: If the given logical test is FALSE, 5% is the incentive percentage.

The completed code is as shown below:

Sub IFF_Ex1()
Range(“C2”).Value = IIf(Range(“B2”).Value > Range(“A2”).Value, “10%”, “5%”)
End Sub

Step 7: Execute the code by pressing the green triangular Execute button in the Visual Basic Editor window or the shortcut key F5.

We get the following result for the incentive percentage in cell C2.

The incentive percentage returned by the VBA IIF function is 5%.

Explanation:

The logical test applied was: IIf(Range(“B2”).Value > Range(“A2”).Value)

Take a look at the Excel data.

The value in cell B2 is $4,500, which is less than the value in cell A2, so the logical test failed. Hence, the VBA IIF function has returned the false statement result, i.e., 5%.

Example #2: IF vs IIF

The first confusion among programmers would be the difference between IF and IIF. Both are logical functions yet applying them for testing differs slightly.

The first difference is that “IF is a logical statement whereas VBA IIF is a logical function.” A statement is like an instruction executed, while a function is a sub-routine that may or may not return a value.

Let’s take the same data from Example #1 for better demonstration.

IF Condition Code:

Sub IFF_Ex2()
If Range(“B2”).Value > Range(“A2”).Value Then
Range(“C2”).Value = “10%”
Else
Range(“C2”).Value = “5%”
End If
End Sub

IIF Function Code:

Sub IFF_Ex1()
Range(“C2”).Value = IIf(Range(“B2”).Value > Range(“A2”).Value, “10%”, “5%”)
End Sub

In terms of code, the IF statement looks long and tougher to understand. However, the VBA IIF function is a single line of code and easy to understand. Let us explain the IF statement code first.

Line #1: If Range(“B2”).Value > Range(“A2”).Value Then

Here, we have applied the logical condition to check if the value in cell B2 is greater than the value in cell A2, followed by the “then” keyword.

Line #2: Range(“C2”).Value = “10%”

If the logical test applied in line #1 is true, the “Then” inserts a value “10%” in cell C2.

Line #3: Else

If the logical test in line #1 is false, we will use the “Else” statement to decide the result.

Line #4: Range(“C2”).Value = “5%”

If the logical test is false, we will insert 5% in cell C2.

Line #5: End If

Here, we close the IF statement using the “End If” keyword. In a nutshell, IF requires multiple lines of code, whereas the IIF function requires formula-like arguments to function. However, the result of both methods is the same.

Example #3: VBA Nested IIF Condition

We have learnt so far, the functionality of the IIF condition. However, the IIF condition can do much more and be applied for complex logical tests, i.e., nested conditions.

For example, look at the following data in Excel.

We have an employee, “Peter,” in cell A2 and his current designation, “Manager,” in cell B2. In a recent performance evaluation cycle, he obtained a rating of 3, mentioned in cell C2. We must calculate his next designation level based on the rating with the following conditions.

• If the rating is greater than or equal to 5, then his designation will be “Head of Department.”
• If the rating is greater than or equal to 4, then his designation will be “Assistant VP.”
• If it is greater than or equal to 3, then his designation will be “Senior Manager.”
• Anything less than 3, does not change his current designation.

To find his new designation, we need to evaluate four different conditions. First, follow the steps below to apply nested IIF in VBA.

Step 1: Here, the result is written in cell D2. So, let us refer to the cell’s address and use the Value property to set the value returned by the IIF condition.

Step 2: Enter the VBA IIF condition after the equal to sign.

Step 3: The first condition we must test is if the rating in cell C2 is greater than or equal to 5. Apply the logical test in excel as shown below.

Step 4: If the condition evaluates to true, we must get the result “Head of Department.” Hence, provide this result in double quotes of the TruePart of the IIF function.

Step 5: Next, if the applied condition is false, we must test the next condition, i.e., if the rating in cell C2 is greater than or equal to 4.

To apply this test, enter another IIF condition in the FalsePart argument of the IIF function.

Step 6: For the inner IIF condition, apply the second logical test, as shown below.

Step 7: If the inner logical test is TRUE, we must get “Assistant VP” as a result.

Step 8: If it is false, we must apply the next logical test, i.e., we must test if the rating in cell C2 is greater than or equal to 3.

For this, enter another IIF condition and apply the logical test. If it is TRUE, we must get “Senior Manager” as a result.

Step 9: If all the applied conditions in the nested IIF conditions are false, we must get the same designation without change. So, enter “Manager” in the “False Part” of the current innermost IIF condition.

Step 10: Close the brackets of all the IIF conditions, i.e., the three IIF conditions entered, to complete the IIF condition nested view.

The complete IIF code looks like this now.

Sub IFF_Ex3()
Range(“D2”).Value = IIf(Range(“C2”).Value >= 5, “Head of Department”, IIf(Range(“C2”).Value >= 4, “Assistant VP”, _
IIf(Range(“C2”).Value >= 3, “Senior Manager”, “Manager”)))
End Sub

Step 11: Execute the code, and we will get the following result in cell D2.

The result is “Senior Manager.” It is because the rating is three, and the third IIF condition holds TRUE. Hence, the nested IIF function returns “Senior Manager.”

We can change the employee’s rating in cell C2 and rerun the code to get different results.

Example #4: Loop Through Cells with the Same IIF Condition

So far, we have worked on single-cell data. However, in a real-world scenario, we will have more than one data cell to test. Then, we cannot write the same logic multiple times. Hence, we use loops to minimize the lines of code.

For example, look at the following data in Excel.

We have more than one cell to test, so let us use the loop concept here.

Sub IFF_Ex4()
Dim k As Long
Dim LR As Long
‘Find the last used row
LR = Cells(Rows.Count, 1).End(xlUp).Row
‘Use loops to loop thorugh all the cells
For k = 2 To LR
Range(“D” & k).Value = IIf(Range(“C” & k).Value >= 5, “Head of Department”, IIf(Range(“C” & k).Value >= 4, “Assistant VP”, _
IIf(Range(“C” & k).Value >= 3, “Senior Manager”, Range(“B” & k).Value)))
Next k
End Sub

It will dynamically loop through all the cells and return the result as shown below.

Important Things to Note

• IF and IIF return similar results, but their application is different.
• VBA IIF is a replica of the worksheet IF function, but it evaluates both the true and false parts and returns a single result.
• With VBA IIF, we need not mention “End IIF” whereas it is required with the IF statement.
• The IIF condition returns an error if the required brackets are not closed in multiple IIF statements.

Frequently Asked Questions (FAQs)

Download Template

This article must be helpful to understand the VBA IIF, with its formula and examples. You can download the template here to use it instantly.

Recommended Articles

Guide to VBA IIF. Here we explain how to use it, its syntax, with step-wise examples and downloadable excel template. You may learn more from the following articles –

• VBA Set Statement
• VBA AND Function
• VBA Case Statement