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

[code]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[/code]
(can safely replace Visual Basic's standard IIf)

Useful code optimization transitions:

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

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

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

Example?

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

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

Nice peice of code Spht, five stars from me!

[quote author=Spht link=board=31;threadid=4004;start=0#msg33490 date=1070488257]
I guess in cases like that you'd use 0 instead of leaving it blank.
[/quote]

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?

How about this:

[code]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[/code]

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.

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

[quote author=Adron link=board=31;threadid=4004;start=0#msg34248 date=1070880473]Is there no way to call global functions in VB?[/quote]

Eh?

[quote author=Spht link=board=31;threadid=4004;start=0#msg34645 date=1071089906]
[quote author=Adron link=board=31;threadid=4004;start=0#msg34248 date=1070880473]Is there no way to call global functions in VB?[/quote]

Eh?
[/quote]

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

[quote author=Adron link=board=31;threadid=4004;start=0#msg34664 date=1071094469]
[quote author=Spht link=board=31;threadid=4004;start=0#msg34645 date=1071089906]
[quote author=Adron link=board=31;threadid=4004;start=0#msg34248 date=1070880473]Is there no way to call global functions in VB?[/quote]

Eh?
[/quote]

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
[/quote]

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

[quote author=Spht link=board=31;threadid=4004;start=0#msg34671 date=1071095425]

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

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

[quote author=Adron link=board=31;threadid=4004;start=0#msg34674 date=1071096133]
[quote author=Spht link=board=31;threadid=4004;start=0#msg34671 date=1071095425]

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

In C++ you could, using namespaces and the :: operator...
[/quote]

Oh. Well, we could do this:

[code]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[/code]

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

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