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

 

[Charlotte E]

How about this:

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

(Works on my Excel 2003)

CE

 

[Ron Rosenfeld]

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

I merely copied and pasted your UDF into a regular module, and it
worked without any problem. Perhaps there is some other issue than
the UDF syntax.

As an aside, it is a bit shorter (eliminates the IF statement) to
write your UDF this way:

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

 

[Charlotte E]

As an aside, it is a bit shorter (eliminates the IF statement) to

write your UDF this way:

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

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

 

[Dave Peterson]

You got responses to your question.

You may want to remove the "application.volatile" line.

It looks like the function (in every response, too) is being passed everything
it needs to determine when to recalculate.

 

[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.

 

[Ron Rosenfeld]

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

Of course, the user did not specify that he wanted to exclude a
negative number for the Periods variable.

However, if you feel data entry requires validation, why limit the
validation to just the Periods value, and why limit that to only
positive values?