Hi Stuie,

I can't recreate your #VALUE! error with your function in an addin and
omitting the optional arguments in the cell formula. However with missing
variants Cstr(Empty) returns a "Error 448" in the returned string for each
usage.

In your addin make a new function with slightly different name, eg ITEMLIST2
(don't forget to change in the two places where you assign a return value).
Also make the changes I suggested last time (ie variant to string). Does
this work.

Regards,
Peter T

wrote in message
...
Peter,

Thanks for your reply.

When I save the file as an Add-In i can still call the UDF, however I
need to enter values for all of the "Optional" arguments for it to
work, otherwise i get the #VALUE! error.

I read an article / post suggesting that Optional arguments need to be
declared as variants in an Add-In.
Tried declaring as a string & got the same result.

Any other suggestions would be great.

Cheers

Stuie


On 13 Dec, 10:44, "Peter T" <peter_t@discussions wrote:
What do you mean by "as soon as I save the Workbook as an Add-In the
Optional variables become Required!". Eg, the 'Optional' prefixes are
removed in the list of function arguments, an error occurs when used as
a
UDF and if so where does it error, can't find the name of the UDF in a
cell
formula, or something else.

In passing I'd change

Optional ByVal ITEM_PREFIX As Variant, Optional ByVal ITEM_SUFFIX As
Variant
pStrList = pStrList & CStr(ITEM_PREFIX) & CStr(pRngCell.Value) &
CStr(ITEM_SUFFIX)

to

Optional ByVal ITEM_PREFIX As String, Optional ByVal ITEM_SUFFIX As
String
pStrList = pStrList & ITEM_PREFIX & CStr(pRngCell.Value) & ITEM_SUFFIX

Regards,
Peter T