WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA IIF

VBA IIF

By Jeevan A YJeevan A Y | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

Excel VBA IIF

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

What Does IIF Condition Do in VBA?

This is very similar to our IF condition but slightly different in nature. “VBA IIF” condition tests the supplied the expression or logical test and returned either TRUE or FALSE as a result.

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, then what should be the TRUE part.
  • False Part: If the logical test is FALSE, then what should be the result of the FALSE part.

We can enter our own results with TRUE & FALSE parts. Though arguments look similar to that of the IF condition, this will be slightly different. We will see that in the examples of 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 the same result.

vba-iif

Example of VBA IIF Function

Below are the examples of VBA IIF Function in excel.

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

Example #1 – VBA IIF

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

Popular Course in this category
Sale
VBA Training (3 Courses, 12+ Projects)
4.6 (247 ratings)
3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
View Course

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: Now, if the expression is TRUE, what should be the result. I will assign the result as “Number 1 is greater than Number 2”.

example 1.6

Step 8: Now, if the expression is FALSE, what should be the result. I 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’s 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’s 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”. 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 & 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 the functions return the same result, but with IIF, we can code in a single line only, 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 use multiple IIF as well. 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 has been a guide to VBA IIF. Here we discuss how to use Excel VBA IIF Function with the help of practical examples and a downloadable excel template. Below are some useful articles related to VBA –

  • VBA Charts
  • IF OR in VBA
  • IFERROR in VBA
  • VBA Tutorial for Beginners
0 Shares
Share
Tweet
Share
VBA Training (3 Courses, 12+ Projects)
  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download VBA IIF Excel Template

Special Offer - VBA Training Course (6 courses, 35+ hours video) View More