EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home VBA VBA Resources VBA Program Flow Functions VBA IIF
 

VBA IIF

Madhuri Thakur
Article byMadhuri Thakur

VBA IIF

Excel VBA IIF Function

VBA IIF (also known as Immediate If) is a statement which you might have often seen while coding under VBA and creating macros. It is similar to Excel’s IF function where you write down a logical condition and gives two outputs, if the condition is true and if the condition is false. If by looking at it you are thinking it as a function similar to VBA IF which we use to evaluate logical tests and conditions, you are mistaken. It seems to be close to VBA If and ideally, one can say that they really have a small difference between them during execution. In this article, we will get more information about VBA IIF statement and how to use it in our day to day coding life to make our tasks easier.

 

 

VBA IIF statement works similarly to Excel IF statement. It checks the condition or logical statement provided and gives output either associated with the condition TRUE or when the condition is FALSE.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Syntax of IIF in Excel VBA

The syntax for VBA IIF function in excel is as follows:

Syntax VBA IIF

Where,

  • Expression: is the logical condition we wanted to evaluate under IIF function
  • TruePart: is the value/output we expect, when the logical condition/expression is TRUE.
  • FalsePart: is the value/output we expect, when the logical condition/expression is FALSE.

How to Use Excel VBA IIF?

Now let us try with some examples on VBA IIF in Excel.

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

Let’s take a simple example to see how IIF work under Microsoft VBA.

Example #1 – VBA IIF

Step 1: Open a Visual Basic Editor (VBE). Go to Insert tab and click on Module. It will add a new module under VBE.

VBA IIF Example 1.1

Step 2: Define a new sub-procedure which can hold your macro in this module.

Code:

Sub IIf_Ex1()

End Sub

VBA IIF Example 1.2

Step 3: Define two new variables Var_1 as Long and Result with data type as a variant.

Code:

Sub IIf_Ex1()

Dim var_1 As Long
Dim Result As Boolean

End Sub

VBA IIF Example 1.3

Step 4: Assign a numeric value to Var_1 so that we can use this variable to check the logical IIF condition.

Code:

Sub IIf_Ex1()

Dim var_1 As Long
Dim Result As Boolean
var_1 = 5

End Sub

VBA IIF Example 1.4

Step 5: Use Result variable to store the logical IIF condition under which we are going to check if the value assigned to Var_1 is greater than or equals to 10.

Code:

Sub IIf_Ex1()

Dim var_1 As Long
Dim Result As Boolean
var_1 = 5
Result = IIf(var_1 >= 10, True, False)

End Sub

Example 1.5

Step 6: Now, use Debug.Print to print the result of IIF condition on immediate result viewer pane.

Code:

Sub IIf_Ex1()

Dim var_1 As Long
Dim Result As Boolean
var_1 = 5
Result = IIf(var_1 >= 10, True, False)
Debug.Print Result

End Sub

Example 1.6

Step 7: Run this code by hitting F5 or Run button at the top of the pane and see the output under Immediate output pane.

VBA IIF Example

Example #2 – VBA IIF

Suppose we have data under worksheet as follows:

VBA IIF Example 2.1

All we want is an output under column B such that, either the number is even or odd.

Step 1: Define a new sub-procedure under VBE.

Code:

Sub IIF_Ex2()

End Sub

VBA IIF Example 2.2

Step 2: Define two variables “a” and “Numbers” as Long.

Code:

Sub IIF_Ex2()

Dim a As Long
Dim Number As Long

End Sub

Example 2.3

Step 3: Start a For loop under which we want to loop all the values present in cell A2:A11. Loop will start from 2 to 11 (because we have headers in row 1 for column A and B).

Code:

Sub IIF_Ex2()

Dim a As Long
Dim Number As Long
For a = 2 To 11

End Sub

Example 2.4

Step 4: Use Number Variable defined earlier to store all the numbers from column A varying on the given range using assignment operator (A2:A11). Use the following line of code to do so.

Number = Sheet1.Range(“A” & a)

Code:

Sub IIF_Ex2()

Dim a As Long
Dim Number As Long
For a = 2 To 11
Number = Sheet1.Range("A" & a)

End Sub

Example 2.5

This line of code allows VBA to run through all the rows in column A from “Sheet1” in excel one by one under For loop.

Step 5: Use IIF to check whether each cell value is even or odd and store the results under each cell of column B. Following line of code will do the task for you.

Sheet1.Range(“B” & a).Value = IIf(Number Mod 2 = 0, “Even”, “Odd”)

Code:

Sub IIF_Ex2()

Dim a As Long
Dim Number As Long
For a = 2 To 11
Number = Sheet1.Range("A" & a)
Sheet1.Range("B" & a).Value = IIf(Number Mod 2 = 0, "Even", "Odd")

End Sub

VBA IIF Example 2.6

Here, in this line, we want the results to be stored under each cell of column B from Sheet1. Therefore we used “Sheet1.Range(“B” & i).Value” on the left-hand side of the piece of code. We used IIF to check whether the number is divisible by 2 using Mod (Modulo operator under VBA). Finally, we want the output as to whether the number is Even or Odd in column B. Therefore, these two values are mentioned under the IIF statement.

Step 6: Close the For loop with Next statement so that after every iteration, the system will move towards the next cell in sheet, until it reaches the 11th row.

Code:

Sub IIF_Ex2()

Dim a As Long
Dim Number As Long
For a = 2 To 11
Number = Sheet1.Range("A" & a)
Sheet1.Range("B" & a).Value = IIf(Number Mod 2 = 0, "Even", "Odd")
Next

End Sub

Example 2.7

Step 7: This is it. Run this code by hitting F5 or Run button placed at the uppermost pane of VBE. Once you run this macro, you’ll see an output as below under sheet1 (named as “Example_1”) of excel workbook.

VBA IIF 2.8

Example #3 – VBA IIF

We will see a nested IIF statement now:

This is the same way we nest multiple IF conditions in a single loop.

Suppose the same data as we used in the previous case. All we wanted is to write a code which allows us to distinguish between numbers as follows:

  • If the number is between 1 and 3 (3 Included), it should mention “Small” under column B.
  • If the number is between 4 and 6 (6 Included), it should mention “Medium” under column B.
  • If the number is between 7 and 10 (10 Included), it should mention “Large” under column B.

Let’s write a code for this type of IIF:

Step 1: Define a new sub-procedure under your VBE which can hold your macro.

Code:

Sub NestedIf()

End Sub

VBA IIF Example 3.1

Follow Step 2 to Step 4 same as in the above example (Example 2 in this article). It includes defining variables and adding all the numbers present in column A under a For loop.

Step 5: Use the following piece of code to get the desired output.

Code:

Sub NestedIf()

Dim Number As Long
For a = 2 To 11
Number = Sheet2.Range("A" & a)
Sheet2.Range("B" & a).Value = IIf(Number <= 3, "Small", IIf(Number >= 7, "Large", "Medium"))

End Sub

Example 3.2

Step 6: Close the For loop with Next statement and run this code using F5 or Run button present at the upper ribbon under VBE. Once you run the code, you will see the output as below:

VBA IIF 3

Here, in this code, the nested IIF is being used. Under the first IIF, we are given what should be printed under column B when the numbers are between 1 and 3. Under second IIF, we mentioned what should be printed under column B when the numbers are greater than or equals to 7 and under the same IIF, we provided what should be printed under column B when the numbers are not between 1 to 3 and 7 to 10.

In this way, we can add multiple IIF’s under the same code and nest them. This is it from this article. Let’s wrap the thing up with some things to be remembered.

Things to Remember

  • IIF always evaluates both parts (TRUE and FALSE) for a particular condition. However, it only prints the FALSE part when nothing is TRUE.
  • Writing is shortened than that of standard If-Else statements.
  • It is not well known, therefore some user might not understand your code if you have used IIF instead of conventional If-Else.

Recommended Articles

This is a guide to VBA IIF. Here we discuss how to use Excel VBA IIF along with practical examples and downloadable excel template. You can also go through our other suggested articles –

  1. VBA Active Cell
  2. VBA Delete Row
  3. VBA Transpose
  4. VBA RGB

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

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

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Web Development & many more.

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

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

EDUCBA

Download VBA IIF Excel Template

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW