Excel / VBA IIf Function: Explained with 3 Examples
The IIf function (capital II and small f) can be taken as a simple form of If..Then..Else statements.
IIf function in VBA
The IIf function (capital II and small f) can be taken as a simple form of VBA If..Then..Else statements.
Just like usual IF..Then..Else statement, we provide an expression to the IIf function that we need to be tested.
If the expression is evaluated as True, the True part is executed.
If expression is False, the False part executes.
See the syntax below:
IIf(expr, truepart, falsepart)
The examples below will make it clearer.
A simple example of using IIF function in VBA
We have two variables in the VBA program:
x= 20
b = 30
Our expression in the IIf is:
a > b
See the program and its output in the message box:
Sub IIf_ex() Dim a Dim b a = 20 b = 30 MsgBox (IIf(a > b, "True, a is greater than b!", "False, a is not greater than b!")) End Sub
Result:
As variable a's value is less than b, so expression is evaluated as False.
Using a text value in a sheet cell example
In this example, we will use the A2 cell value in the expression.
Based on the True/False result, we will write a value in the B2 cell:
Sub IIf_ex()
Range("B2").Value = IIf(Range("A2").Value = "Black", "Yes It is black!", "No it is not Black")
End Sub
Result:
As A2 cell text is Black, it is evaluated as True and so the True part’s string is written in the B2 cell.
Using Range of cells in IIf function to check even/odd numbers
In this program, we will use a range of cells (A2:A10) that contain different numbers.
By using For..Each loop, we will iterate through each cell in the range.
In each iteration, the number in the cell is checked in the IIf function – whether it's Even or Odd.
Even/Odd is written in the adjacent B cell:
Sub IIf_ex()
Dim rng_iif As Range
Dim x
Set rng_iif = Range("A2:A10")
x = 2
For Each cell In rng_iif
Range("B" & x).Value = IIf(cell.Value Mod 2 = 0, "Even", "Odd")
x = x + 1
Next
End Sub
Result:
You can see, we used the Mod function for each cell in the range inside the IIf function.
Changing the background color of cells in range by using IIf
In this example, we will change the font color of cells with the text “Out of Stock” to red – in the “Product Information” sheet. While the “In Stock” text is made blue by using IIf function.
VBA code:
Sub IIf_ex()
Dim rng_iif As Range
Dim x
'Status column range
Set rng_iif = Range("D2:D11")
x = 2
'Making cells blue and red based
For Each cell In rng_iif
cell.Font.ColorIndex = IIf(cell.Value = "Out of Stock", 3, 5)
x = x + 1
Next
End Sub
Result:
How did it work?
- The value of each cell is tested.
- If it is “Out of Stock”, IIf returned 3
- The value 3 (red code) is assigned to the ColorIndex property of Font to make the text red.
- If text is not “Out of Stock” IIf returned 5 (blue color code)
