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.

Syntax of IIF in Excel VBA

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

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.

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.

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

Code:

Sub IIf_Ex1()

End Sub

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

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

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

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

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.

Example #2 – VBA IIF

Suppose we have data under worksheet as follows:

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

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

Code:

Sub IIF_Ex2()

Dim a As Long
Dim Number As Long

End Sub

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

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

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

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 11^th 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

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.

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

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

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:

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. Working with VBA Active Cell
2. Deleting a Row in VBA
3. How to Use Excel VBA Transpose?
4. How to Fix 1004 Error Using VBA