The VBA IIf Function

Related Function:

Description

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:

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.

VBA IIf Function Examples

Example 1

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:

								testVal = -2

								sign1 = IIf( testVal < 0, "negative", "positive" )

    						' sign1 is now equal to "negative".

' Second call to IIf function. The test value is positive:
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.

Example 2 - Nested IIf Function

The following VBA code shows a nested IIf function.

								' Test if a Supplied Integer is Positive, Negative or Zero.

  							Dim testVal As Integer

  							Dim sign1 As String

testVal = -2
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".

Return to the VBA Functions Page
Return to the Excel VBA Tutorial Page