Simply allows the TruePart and FalsePart to be optional in IIf().
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:
MsgBox "Bill has " & apple & " apple" & IIf(apple = 1, Empty, "s")
Update:
MsgBox "Bill has " & apple & " apple" & IIf(apple = 1, , "s")
MsgBox "Bill has " & apple & " apple" & IIf(apple <> 1, "s", Empty)
Update:
MsgBox "Bill has " & apple & " apple" & IIf(apple <> 1, "s")
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 from: Spht on December 03, 2003, 03:50 PM
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?
How about this:
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.
That's nice! I didn't know vbEmpty. Is there no way to call global functions in VB?
Quote from: Adron on December 08, 2003, 04:47 AMIs there no way to call global functions in VB?
Eh?
Quote from: Spht on December 10, 2003, 02:58 PM
Quote from: Adron on December 08, 2003, 04:47 AMIs 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
Quote from: Adron on December 10, 2003, 04:14 PM
Quote from: Spht on December 10, 2003, 02:58 PM
Quote from: Adron on December 08, 2003, 04:47 AMIs 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.
Quote from: Spht on December 10, 2003, 04:30 PM
No, can't do that unless we rename our IIf() function to something else.
In C++ you could, using namespaces and the :: operator...
Quote from: Adron on December 10, 2003, 04:42 PM
Quote from: Spht on December 10, 2003, 04:30 PM
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:
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)
Ah, yes, that's exactly what I meant! It looks much better to define the improved IIf in terms of IIf ;)