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:



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:



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:



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:



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)