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

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


"RalphH" wrote in message
...
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

On Wed, 11 Aug 2010 20:50:07 -0700 (PDT), RalphH
wrote:

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
=====================

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

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.



On 08/11/2010 22:50, RalphH wrote:
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


--
Dave Peterson

On Aug 12, 6:33*am, "Charlotte E" wrote:
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

Thanks! This works fine.

On Thu, 12 Aug 2010 15:33:15 +0200, "Charlotte E"
wrote:

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


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?