UDF Optional variables become required in Add-In

[stuart]

Hello there...

I've written a UDF that allows me to concantenate a range of cell
values with a prefix & suffix value. See below:



Public Function ITEMLIST(ByVal CELL_RANGE As Range, Optional ByVal
ITEM_PREFIX As Variant, Optional ByVal ITEM_SUFFIX As Variant) As
String
'#### Concatenate range of cells, incorporating specified prefix &
suffix values ####

Dim pRngCell As Range
Dim pStrList As String

On Error GoTo errCode

For Each pRngCell In CELL_RANGE
pStrList = pStrList & CStr(ITEM_PREFIX) & CStr(pRngCell.Value)
& CStr(ITEM_SUFFIX)
Next
ITEMLIST = pStrList
Exit Function

errCode:
ITEMLIST = "# Unable to list!"
End Function


The problem i have is that it works fine in an Excel Workbook, but as
soon as I save the Workbook as an Add-In the Optional variables become
Required!

If anyone can shed any light on this i'd be very grateful...

Rgds

Stuie

 

[Peter T]

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

 

[monakee]

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

 

[Peter T]

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

 

[monakee]

Peter,

Apologies, I was being a bit dense!
Didn't realise there was an older version of the Add-In still being
referenced!

Your suggested adjustment works just fine...

Thanks again

Stuie

On 13 Dec, 12:18, "Peter T" <peter_t@discussions> wrote: