`IIf` Function

Returns one of two parts, depending on the evaluation of an expression.

Syntax

IIf(expr, truepart, falsepart)

Parameters

expr (Required): Expression you want to evaluate
truepart (Required): Value or expression returned if expr is True
falsepart (Required): Value or expression returned if expr is False

Return Value

Returns truepart if expr evaluates to True; otherwise returns falsepart. The return type is Variant and depends on the types of truepart and falsepart.

Remarks

The IIf function provides inline conditional evaluation:

Always evaluates BOTH truepart and falsepart regardless of the condition result
This can cause side effects if either part contains function calls or property accesses
Returns Variant type, which may require explicit type conversion
Can nest IIf calls for multiple conditions (though readability suffers)
If expr is Null, the function returns Null
Unlike If...Then...Else statements, IIf is an expression that returns a value
Useful for inline assignments, but beware of evaluation side effects
Consider using If...Then...Else for complex logic or when side effects matter

Typical Uses

Inline Conditionals: Simple conditional value assignment in one line
String Formatting: Choose between different string representations
Calculated Fields: Conditional calculations in expressions
Default Values: Provide fallback values for empty or null data
Display Logic: Choose display text based on conditions
Data Validation: Return appropriate values based on validation results

Basic Usage Examples

' Example 1: Simple conditional assignment
Dim result As String
result = IIf(age >= 18, "Adult", "Minor")

' Example 2: Numeric comparison
Dim status As String
status = IIf(score >= 60, "Pass", "Fail")

' Example 3: Null handling
Dim display As String
display = IIf(IsNull(value), "N/A", CStr(value))

' Example 4: Sign determination
Dim sign As String
sign = IIf(number >= 0, "+", "-")

Common Patterns

' Pattern 1: Choose singular or plural
Function FormatCount(count As Long, singular As String, plural As String) As String
FormatCount = count & " " & IIf(count = 1, singular, plural)
End Function
' Usage: FormatCount(5, "item", "items") => "5 items"

' Pattern 2: Min/Max selection
Function Min(a As Double, b As Double) As Double
Min = IIf(a < b, a, b)
End Function

Function Max(a As Double, b As Double) As Double
Max = IIf(a > b, a, b)
End Function

' Pattern 3: Safe division
Function SafeDivide(numerator As Double, denominator As Double) As Variant
SafeDivide = IIf(denominator <> 0, numerator / denominator, Null)
End Function

' Pattern 4: Empty string default
Function GetDisplayName(name As String) As String
GetDisplayName = IIf(Len(Trim$(name)) > 0, name, "(unnamed)")
End Function

' Pattern 5: Range clamping
Function Clamp(value As Long, minVal As Long, maxVal As Long) As Long
Clamp = IIf(value < minVal, minVal, IIf(value > maxVal, maxVal, value))
End Function

' Pattern 6: Boolean to integer
Function BoolToInt(value As Boolean) As Integer
BoolToInt = IIf(value, 1, 0)
End Function

' Pattern 7: Sign function
Function Sign(value As Double) As Integer
Sign = IIf(value > 0, 1, IIf(value < 0, -1, 0))
End Function

' Pattern 8: Null coalescing
Function Coalesce(value As Variant, defaultValue As Variant) As Variant
Coalesce = IIf(IsNull(value) Or IsEmpty(value), defaultValue, value)
End Function

' Pattern 9: Conditional formatting
Function FormatBalance(balance As Currency) As String
FormatBalance = IIf(balance < 0, _
"(" & Format$(Abs(balance), "Currency") & ")", _
Format$(balance, "Currency"))
End Function

' Pattern 10: Toggle value
Function Toggle(current As Boolean) As Boolean
Toggle = IIf(current, False, True)
End Function

Advanced Usage Examples

' Example 1: Grade calculator with nested IIf
Function GetGrade(score As Double) As String
GetGrade = IIf(score >= 90, "A", _
IIf(score >= 80, "B", _
IIf(score >= 70, "C", _
IIf(score >= 60, "D", "F"))))
End Function

' Example 2: Complex string builder
Function BuildMessage(userName As String, isAdmin As Boolean, messageCount As Long) As String
BuildMessage = "Welcome " & IIf(Len(userName) > 0, userName, "Guest") & _
IIf(isAdmin, " (Admin)", "") & _
IIf(messageCount > 0, " - You have " & messageCount & " message" & _
IIf(messageCount = 1, "", "s"), "")
End Function

' Example 3: Data validation with IIf
Function ValidateAndFormat(input As String, Optional maxLen As Long = 50) As String
Dim cleaned As String
cleaned = Trim$(input)

ValidateAndFormat = IIf(Len(cleaned) = 0, "", _
IIf(Len(cleaned) > maxLen, _
Left$(cleaned, maxLen) & "...", _
cleaned))
End Function

' Example 4: Status indicator with color codes
Function GetStatusDisplay(value As Double, threshold As Double) As String
Dim status As String
Dim color As String

status = IIf(value >= threshold, "OK", "WARNING")
color = IIf(value >= threshold, "Green", "Red")

GetStatusDisplay = "[" & color & "] " & status & " (" & value & ")"
End Function

' Example 5: Conditional object creation (DANGEROUS - both parts evaluate!)
' WARNING: This pattern has side effects!
Function GetConnection(useLocal As Boolean) As Object
' BOTH CreateLocalConnection AND CreateRemoteConnection will execute!
' Use If...Then...Else instead for object creation
Set GetConnection = IIf(useLocal, CreateLocalConnection(), CreateRemoteConnection())
End Function

' Example 6: Safe property access
Function GetPropertyValue(obj As Object, propertyName As String, defaultValue As Variant) As Variant
On Error Resume Next
Dim value As Variant
value = CallByName(obj, propertyName, VbGet)

If Err.Number = 0 Then
GetPropertyValue = IIf(IsNull(value), defaultValue, value)
Else
GetPropertyValue = defaultValue
End If
On Error GoTo 0
End Function

Error Handling

The IIf function itself rarely raises errors, but be aware of:

Type Mismatch (Error 13): Can occur if the result type doesn't match the receiving variable
Evaluation Errors: Both truepart and falsepart are always evaluated, so errors in either will occur
Null Propagation: If expr is Null, IIf returns Null
Division by Zero: Can occur if either part contains division and is evaluated

' WRONG - Both divisions execute regardless of condition!
result = IIf(denominator <> 0, numerator / denominator, numerator / 1)
' If denominator is 0, division by zero error still occurs in first part

' CORRECT - Use If...Then...Else for conditional execution
If denominator <> 0 Then
result = numerator / denominator
Else
result = numerator / 1
End If

Performance Considerations

Both Branches Evaluate: IIf always evaluates both truepart and falsepart
Function Call Overhead: IIf has function call overhead vs. If...Then...Else
Variant Boxing: Results are Variant type, which may require type conversion
Nested Performance: Deeply nested IIf calls can be slow and hard to read
Use If...Then...Else When: Either branch has expensive operations or side effects

Best Practices

Avoid Side Effects: Don't use IIf when either part has side effects (function calls, object creation, I/O)
Keep It Simple: Use IIf for simple value selection only
Limit Nesting: Avoid deeply nested IIf calls (use Select Case or If...Then...Else instead)
Type Safety: Be aware of Variant return type and convert explicitly if needed
Readability: If IIf makes code harder to read, use If...Then...Else
Document Expectations: When using IIf, document that both branches evaluate

When NOT to Use `IIf`

' DON'T: Object creation (both execute!)
Set obj = IIf(condition, New ClassA, New ClassB)

' DON'T: Functions with side effects (both execute!)
result = IIf(condition, LogAndReturn("A"), LogAndReturn("B"))

' DON'T: Error-prone operations (both execute!)
value = IIf(x <> 0, 100 / x, 0)  ' Division by zero still occurs!

' DON'T: Complex nested logic (hard to read)
result = IIf(a, IIf(b, IIf(c, 1, 2), IIf(d, 3, 4)), IIf(e, 5, 6))

' DO: Use If...Then...Else instead
If condition Then
Set obj = New ClassA
Else
Set obj = New ClassB
End If

Comparison with Other Approaches

Approach	Evaluates Both	Return Type	Use Case
`IIf`	Yes	`Variant`	Simple inline value selection
`If...Then...Else`	No	Any	Conditional execution, side effects
`Select Case`	No	Any	Multiple conditions
`Choose`	Yes	`Variant`	Index-based selection

Platform and Version Notes

Available in all VB6 versions
Consistent behavior across Windows platforms
VBA also includes IIf with identical behavior
Always returns Variant type
Evaluation of both branches is by design, not a bug

Limitations

Cannot short-circuit evaluation (both parts always execute)
Returns Variant type (requires explicit conversion for strong typing)
Not suitable for conditional execution (use If...Then...Else)
Nested IIf calls quickly become unreadable
Cannot handle multiple conditions as cleanly as Select Case
May have performance overhead compared to If...Then...Else

If...Then...Else: Statement for conditional execution with short-circuit evaluation
Choose: Returns value from list based on numeric index (also evaluates all parts)
Switch: Returns first value whose expression is True (evaluates sequentially)
Select Case: Multi-condition statement with short-circuit evaluation

IIf Function