ExcelFunctions.net

Search Site:

Related Function:

VBA SWITCHThe VBA IIf function evaluates an expression and returns one of two values, depending on whether the expression evaluates to True or False.

The syntax of the function is:

IIf( Expression, TruePart, FalsePart )

Where the function arguments are:

Expression | - | The expression that is to be evaluated. |

TruePart | - | The value that is to be returned if the supplied Expression evaluates to True. |

FalsePart | - | The value that is to be returned if the supplied Expression evaluates to False. |

The following VBA code shows two simple calls to the IIf function.

' Test if a Supplied Integer is Positive or Negative. Dim testVal As Integer Dim sign1 As String Dim sign2 As String ' First call to IIf function. The test value is negative: ' Second call to IIf function. The test value is positive:testVal = -2 sign1 = IIf( testVal < 0, "negative", "positive" ) ' sign1 is now equal to "negative". testVal = 8 sign2 = IIf( testVal < 0, "negative", "positive" ) ' sign2 is now equal to "positive". |

In the above VBA code:

- In the first call to the IIf function, the expression,
`testVal < 0`

evaluates to True and so the TruePart argument (the String "negative") is returned; - In the second call to the IIf function, the expression,
`testVal < 0`

evaluates to False and so the FalsePart argument (the String "positive") is returned.

The following VBA code shows a nested IIf function.

' Test if a Supplied Integer is Positive, Negative or Zero. testVal = -2Dim testVal As Integer Dim sign1 As String sign1 = IIf( testVal = 0, "zero", IIf( testVal < 0, "negative", "positive" ) ) ' sign1 is now equal to "negative". |

In the above VBA code, there are two calls to the IIf function, one of which is nested inside the other.

- If the outer IIf function evaluates to True, the String "zero" is returned;
If the outer IIf function evaluates to False, the inner IIf function is called:

- If the inner IIf function evaluates to True, the String "negative" is returned;
- If the inner IIf function evaluates to False, the String "positive" is returned.

In the example, the test value is equal to -2 and so the nested IIf function returns the String "negative".