FLASH SALE! - "CHATGPT AND ARTIFICIAL INTELLIGENCE FOR MICROSOFT EXCEL AT 60% OFF" Enroll Now

VBA IIF

Updated on June 7, 2024
Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

Excel VBA IIF

If you are a regular user of VBA macros, you must have come across the function called “IIF,” or you might have seen this function on the internet. At first sight, you must have thought it is an IF condition like our regular IF statement in Excel. But this is not the same IF statement we use to evaluate logical tests and arrive at results based on our criteria. Therefore, this article will take you through the “VBA IIF” condition in VBA.

What Does IIF Condition Do in VBA?

It is very similar to our IF condition but slightly different. For example, the “VBA IIF” condition tests the supplied expression or logical test and returns either TRUE or FALSE.

VBA IIF Syntax

Take a look at the syntax of the IIF function.

vba iif syntax
  • Expression: This is nothing but the logical test we would like to conduct.
  • Ture Part: If the logical test is TRUE, what should be the TRUE part?
  • False Part: If the logical test is FALSE, what should be the result of the FALSE part?

We can enter our results with TRUE and FALSE parts. Though arguments look similar to the IF condition, this will be slightly different. We will see that in the examples of the Excel VBA IIF function.

One of the key differences between regular “IF” and this “IIF” is we can reduce the code to a single line with Iwherewithwith IF condition, and it takes a minimum of 5 lines to arrive at the same result.

VBA IIF

You are free to use this image on your website, templates, etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: VBA IIF (wallstreetmojo.com)

–>> If you want to learn Excel VBA professionally, then our VBA Basic Course (16+ hours) is the perfect solution. In our Basic Excel VBA course you learn the skill of automating tasks using Variables, Data Types, Ranges, and Cells in VBA. Master Control Structures, including Conditional Statements and Loops, and discover techniques for manipulating data through sorting, filtering, and formatting. By the end of the course, you will be able to apply your acquired skills to real projects, culminating in an Excel VBA project which will solidify your ability to create efficient, automated solutions.

Example of VBA IIF Function

Below are examples of the VBA IIF function in Excel.

You can download this VBA IIF Excel Template here – VBA IIF Excel Template

Example #1 – VBA IIF

We will see one simple example of the IIF function. Now, we will test whether one number is greater than or less than another number. Then, follow the below steps to write the VBA code.

Step 1: Start the macro.

Step 2: Define the variable as String in VBA.

Code:

Sub IIF_Example()

Dim FinalResult As String

End Sub
iif example 1.1

Step 3: Define two more variables as Long in VBA.

Code:

Sub IIF_Example()

Dim FinalResult As String

Dim Number1 As Long
Dim Number2 As Long

End Sub
iif example 1.2

Step 4: Now, for the variable “Number1,” assign the value of 105, and for variable “Number2,” assign the value of 100.

Code:

Sub IIF_Example()

Dim FinalResult As String

Dim Number1 As Long
Dim Number2 As Long

Number1 = 105
Number2 = 100

End Sub
iif example 1.3

Step 5: Now, for the first defined variable, “FinalResult,” we will assign the result of the IIF function. So open the IIF for the variable.

iif example 1.4

Step 6: Supply the expression as Number1 > Number2.

iif example 1.5

Step 7: What should be the result if the expression is TRUE? We will assign the result as “Number 1 is greater than Number 2.”

example 1.6

Step 8: What should be the result if the expression is FALSE? We will assign the result as “Number 1 is less than Number 2.”

example 1.7

Now the variable value will be either one of the below.

If True: “Number 1 is greater than Number 2.”

If False: “Number 1 is less than Number 2.”

Step 9: Let us show the result in a message box in VBA.

Code:

Sub IIF_Example()

Dim FinalResult As String

Dim Number1 As Long
Dim Number2 As Long

Number1 = 105
Number2 = 100

FinalResult = IIf(Number1 > Number2, "Number 1 is Greater than Number 2", "Number 1 is Less than Number 2")

MsgBox FinalResult

End Sub
example 1.8

Now, let us run the code and see the result.

example 1.9

Since the Number 1 value is 105, which is greater than the Number 2 value of 100, we got the result as “Number 1 is Greater than Number 2”. Therefore, since the Expression is TRUE, the IIF condition returned this result.

Example #2 – IF vs. IIF

You must be wondering what the difference between IF and IIF is. Yes, there is a difference in coding. For example, look at the IF condition code.

Code:

Sub IIF_Example()

Dim FinalResult As String
Dim Number1 As Long
Dim Number2 As Long

Number1 = 105
Number2 = 100

If Number1 > Number2 Then
MsgBox "Number 1 is Greater than Number 2"
Else
MsgBox "Number 1 is Less than Number 2"
End If

End Sub
example 2.1

Using IF first, we have applied a logical test.

If Number1 > Number2 Then

Then if the logical test is true, we have applied the result.

MsgBox "Number 1 is Greater than Number 2"

Then if the logical test is false, we have applied the different results.

MsgBox "Number 1 is Less than Number 2"

Both functions return the same result, but with IIF, we can only code in a single line where the IF statement requires multiple lines.

Example #3 – VBA Nested IIF Condition

Like how we use nested IF to test multiple conditions similarly, we can also use multiple IIF. Take a look at the below code.

Code:

Sub IIF_Example2()

Dim FinalResult As String

Dim Marks As Long

Marks = 98

FinalResult = IIf(Marks > 90, "Dist", IIf(Marks > 80, "First", IIf(Marks > 70, "Second", IIf(Marks > 60, "Third", "Fail"))))

MsgBox FinalResult

End Sub
example 3

The above IIF condition tests five logical tests and returns the result accordingly.

Recommended Articles

This article has been a guide to VBA IIF. Here, we discuss how to use the Excel VBA IIF function with the help of practical examples and a downloadable Excel template. Below are some useful articles related to VBA: –