The 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:
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:
testVal < 0
evaluates
to True and so the TruePart argument
(the String "negative") is returned;
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 False, the inner IIf function is called:
In the example, the test value is equal to -2 and so the nested IIf function returns the String "negative".