Optional IIf()

[Spht]

Simply allows the TruePart and FalsePart to be optional in IIf().

Code Select Expand

Public Function IIf(ByVal Expression, Optional TruePart, Optional FalsePart)
   If Expression Then
       If IsMissing(TruePart) Then: IIf = "": Else: IIf = TruePart: End If
   Else
       If IsMissing(FalsePart) Then: IIf = "": Else: IIf = FalsePart: End If
   End If
End Function
(can safely replace Visual Basic's standard IIf)

Useful code optimization transitions:

Code Select Expand

MsgBox "Bill has " & apple & " apple" & IIf(apple = 1, Empty, "s")
Update:

Code Select Expand

MsgBox "Bill has " & apple & " apple" & IIf(apple = 1, , "s")

Code Select Expand

MsgBox "Bill has " & apple & " apple" & IIf(apple <> 1, "s", Empty)
Update:

Code Select Expand

MsgBox "Bill has " & apple & " apple" & IIf(apple <> 1, "s")

[Adron]

I like to use IIf in numerical expressions. I don't think returning an empty string will make sense there though...

[Spht]

Example?

[Adron]

Salary = Base + IIf(successful, Bonus, 0) - IIf(debt > 0, debt * 0.08 / 12, 0)

[Spht]

I guess in cases like that you'd use 0 instead of leaving it blank.

[TheMinistered]

Nice peice of code Spht, five stars from me!

[Adron]

I guess in cases like that you'd use 0 instead of leaving it blank.

No way of generalizing this? I was thinking maybe it would be possible to do something like c++ overloading to have it do one thing for numbers passed in and another for strings?

[Spht]

How about this:

Code Select Expand

Public Function IIf(ByVal Expression, Optional TruePart, Optional FalsePart)
   If Expression Then
       If IsMissing(TruePart) Then
           If IsNumeric(FalsePart) Then
               IIf = vbEmpty
           Else
               IIf = Empty
           End If
       Else
           IIf = TruePart
       End If
   Else
       If IsMissing(FalsePart) Then
           If IsNumeric(TruePart) Then
               IIf = vbEmpty
           Else
               IIf = Empty
           End If
       Else
           IIf = FalsePart
       End If
   End If
End Function

Now, if one of the terms is missing and the other term is a number, zero will be returned in the missing term, otherwise an empty string will be returned.

[Adron]

That's nice! I didn't know vbEmpty. Is there no way to call global functions in VB?

[Spht]

Is there no way to call global functions in VB?

Eh?

[Adron]

Is there no way to call global functions in VB?

Eh?

Like the :: operator in C++ ?

Public Function IIf(ByVal Expression, Optional TruePart, Optional FalsePart)

 IIf = IIf(Expression,
    IIf(IsMissing(TruePart), IIf(IsNumeric(FalsePart), vbEmpty, Empty), TruePart),
    IIf(IsMissing(FalsePart), IIf(IsNumeric(TruePart), vbEmpty, Empty), FalsePart))

End Function

[Spht]

Is there no way to call global functions in VB?

Eh?

Like the :: operator in C++ ?

Public Function IIf(ByVal Expression, Optional TruePart, Optional FalsePart)

 IIf = IIf(Expression,
    IIf(IsMissing(TruePart), IIf(IsNumeric(FalsePart), vbEmpty, Empty), TruePart),
    IIf(IsMissing(FalsePart), IIf(IsNumeric(TruePart), vbEmpty, Empty), FalsePart))

End Function

No, can't do that unless we rename our IIf() function to something else.

[Adron]

No, can't do that unless we rename our IIf() function to something else.

In C++ you could, using namespaces and the :: operator...

[Spht]

No, can't do that unless we rename our IIf() function to something else.

In C++ you could, using namespaces and the :: operator...

Oh. Well, we could do this:

Code Select Expand

Public Function IIf(ByVal Expression, Optional TruePart, Optional FalsePart)
   IIf = VBA.IIf(Expression, _
       VBA.IIf(IsMissing(TruePart), VBA.IIf(IsNumeric(FalsePart), vbEmpty, Empty), TruePart), _
       VBA.IIf(IsMissing(FalsePart), VBA.IIf(IsNumeric(TruePart), vbEmpty, Empty), FalsePart))
End Function

(Reference Visual Basic's IIf function directly through VBA)

[Adron]

Ah, yes, that's exactly what I meant! It looks much better to define the improved IIf in terms of IIf