UDF function with Optional Paramters Problem

[RalphH]

Hi;

Below is code for a UDF using an optional parameter. The code works
fine when I supply all the parameters.

for example =FTE(1827,26.1) gives a result of 1

However = FTE(1827) which also give 1 resultsin the error #value:

Function FTE(Hours, Optional Periods)
Application.Volatile
If IsMissing(Periods) Then
Periods = 26.1
End If
FTE = Hours / (Periods * 70)
End Function

any help appreciated.

Thanks

 

[RalphH]

Problem with that solution is that the function is not as 'robust' - the
user can force an error by entering af negative number of periods!

I think you should use this instead, which will trap such errors:

Function FTE(FTE_Hours, Optional FTE_Periods As Double = -1)
    Application.Volatile
    If FTE_Periods <= 0 Then FTE_Periods = 26.1
    FTE = FTE_Hours / (FTE_Periods * 70)
End Function

CE

Thanks! This works fine.