aconcat UDF throwing a #NAME error

[Josh Rogers]

I'm attempting to display in a cell a concatenated list of items that
match in an array. Here is an example:

Data:
A B
1 Bob Chicken
2 Sally Steak
3 Bob Salad
4 Susan Salad
5 Bob Soda


And for the lookup:

A B
1 Bob Chicken Salad Soda
2 Sally Steak
3 Susan Salad

Column B of the lookup is the portion I'm focusing on here.
Originally, i was able to successfully do this using MCONCAT from the
morefunc addon. This worked great, except I am sending this
spreadsheet to others that do not have this addon installed, and it
isn't reasonable to ask them to install it, so I attempted to change
it to the aconcat UDF from Harlan Grove:

Function ACONCAT(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
Dim y As Variant

If TypeOf a Is Range Then
For Each y In a.Cells
ACONCAT = ACONCAT & y.Value & sep
Next y
ElseIf IsArray(a) Then
For Each y In a
ACONCAT = ACONCAT & y & sep
Next y
Else
ACONCAT = ACONCAT & a & sep
End If

ACONCAT = Left(ACONCAT, Len(ACONCAT) - Len(sep))
End Function



This results in a #NAME error. I can't seem to figure out why. I've
grabbed screenshots of the error:

Before Eval: http://imagebin.ca/view/L0mxlr8.html
Step 1 of Eval: http://imagebin.ca/view/ev1f3A1.html

I placed this in 'Module1' at first, then tried placing it in a sheet
module. I'm not sure what else to check. This is my first attempt at
a UDF, so I appreciate any insight. I'm using Excel 2007 (as are many
of the folks that will be viewing the document), but am saving in
Excel 2003 for compatibility.

 

[Ron Rosenfeld]

I'm attempting to display in a cell a concatenated list of items that
match in an array. Here is an example:

Data:
A B
1 Bob Chicken
2 Sally Steak
3 Bob Salad
4 Susan Salad
5 Bob Soda


And for the lookup:

A B
1 Bob Chicken Salad Soda
2 Sally Steak
3 Susan Salad

Column B of the lookup is the portion I'm focusing on here.
Originally, i was able to successfully do this using MCONCAT from the
morefunc addon. This worked great, except I am sending this
spreadsheet to others that do not have this addon installed, and it
isn't reasonable to ask them to install it,

The versions of morefunc that have been released in the last few years have
included the ability to distribute this add-in along with the workbook. It's
not working (yet) in 2007 but if that's not an issue, this may help.

Depending on the options you selected during installation, on the Tools menu
you should have an item labeled Morefunc. ONe of the submenu options is "Embed
morefunc in the workbook"

From Morefunc HELP:

-------------------------
INCLUDING MOREFUNC IN A WORKBOOK
Warning : for the moment (october 2007), the tool described in this page
doesn't work with Excel 2007, because of a bug in a callback function.
Hopefully, it will be fixed by a service pack. Don't use it if you want to
share workbooks using Morefunc with people who work with this Excel version.
It is now possible to include the Morefunc add-in in a workbook, so that the
new functions can be used even if the add-in is not installed. The user has
just to open the workbook, and all functions work as if they were contained in
VBA modules stored in the workbook itself.

HOW TO INCLUDE MOREFUNC IN THE ACTIVE WORKBOOK :
Open the Tools menu, and choose Morefunc => Include Morefunc in the workbook.

This dialog box is opened : **Picture present in help file**



The current version is the version of the Morefunc add-in which is currently
installed on the computer. Included version is the version number of the add-in
already stored in the workbook.

The check boxes Functions and Help file show if Morefunc and its help file are
already present in the active workbook. If you want to include or remove them,
check or uncheck the corresponding options and then click the Update button.

Morefunc will add approximately 350 K to the size of the workbook, and the help
file 150 K.

DETAILED INFORMATIONS :
Embedding Morefunc in a workbook has the following consequences :

It adds a "very hidden" worksheet ("Morefunc Storage Sheet") to the workbook.
The add-in itself and the help file are stored in this sheet as binary data.
It adds a small standard module named modRestoreMorefunc to the VBA project of
the workbook.
It inserts a call to the MorefuncTempInstall Sub in the Workbook_Open event
handler of the workbook.
None of these 3 items should be removed or altered, otherwise the new functions
won't work.

When the workbook is opened, the MorefuncTempInstall sub performs these tasks :

It checks if Morefunc is already installed (and loaded) in the current Excel
instance
If Morefunc is already loaded, it compares its version number with the one of
the Morefunc add-in stored in the workbook.
If the version of the workbook is more recent (or if Morefunc is not
installed), it reads the binary data stored in the hidden sheet, creates a
Morefunc.xll file in the temporary folder and opens it.

--ron

 

[Josh Rogers]

Forgot to mention my formula in 'Lookup'!B1 :

=SUBSTITUTE(SUBSTITUTE(ACONCAT(IF('Data'!$A$1:$A$5='Lookup'!A1,'Data'!
$B$1:$B$5)," ")," FALSE",""),"FALSE","")

(Confirmed with Ctrl-Shift-Enter)

I'm also new to array formula's, so let me know if I'm approaching
this wrong.

 

[Josh Rogers]

The versions of morefunc that have been released in the last few years have
included the ability to distribute this add-in along with the workbook.  It's
not working (yet) in 2007 but if that's not an issue, this may help.

Oh, that would be extremely useful, but unfortunately, I have Excel
2007, and no access to any previous versions. I see the 'Morefunc'
section added to the Excel Ribbon, but it doesn't appear to have an
'Embed morefunc in this workbook' option, so I'm guessing the
incompatibility is with Excel 2007 itslef, and not the newer file
formats the workbook can be saved in.

 

[Ron Rosenfeld]

Oh, that would be extremely useful, but unfortunately, I have Excel
2007, and no access to any previous versions. I see the 'Morefunc'
section added to the Excel Ribbon, but it doesn't appear to have an
'Embed morefunc in this workbook' option, so I'm guessing the
incompatibility is with Excel 2007 itslef, and not the newer file
formats the workbook can be saved in.

That's unfortunate.



In using the ACONCAT function, have you tried it "stand-alone" using just a
simple range as an argument?

I think the problem is in the full path name, and not in ACONCAT itself.

When you enter into your more complex function, do you paste it in from the
formula lookup bar? Or type it in? If the latter, could there be a problem
with the workbook/worksheet reference?

You are correct in that it should be in "Module1" (or some named module and NOT
in a workbook or worksheet module).

But it seems that, since you have entered it with a full path name, that it is
not in the same workbook as the data you are evaluating. If that is the case,
perhaps the workbook is not being "found" in its new location.
--ron

 

[Josh Rogers]

In using the ACONCAT function, have you tried it "stand-alone" using just a
simple range as an argument?

I tried entering =aconcat(A1:A5," ") exactly as is, and also using
Ctrl-Shift-Enter to confirm.

But it seems that, since you have entered it with a full path name, that it is
not in the same workbook as the data you are evaluating.  If that is thecase,
perhaps the workbook is not being "found" in its new location.

The reason you see the full filename, is because when I select the fx
button, and look in user defined, I see my new UDF, and select it, and
Excel specifies the full filename. I've tried with and w/out this
filename. I'll put it back in Module1. The workbook itself is stored
on a sharepoint. I'll try storing it locally to see if it makes any
difference, but I'm afraid I'm grasping at straws here.

-Josh

 

[Harlan Grove]

The versions of morefunc that have been released in the last few
years have included the ability to distribute this add-in along with
the workbook. It's not working (yet) in 2007 . . .

....

There's another potential problem. MOREFUNC is an XLL, and as long as
it's targeted at older versions too, it can't return strings longer
than 255 characters. OTOH, VBA can return strings up to 32767
characters.

 

[Josh Rogers]

than 255 characters. OTOH, VBA can return strings up to 32767
characters.

Harlan, I'm excited to see you respond. Can you give me any tips/
ideas on how to get the aconcat udf working? I'm not even sure what
the error is telling me, at this point.

 

[Harlan Grove]

I'm attempting to display in a cell a concatenated list of items
that match in an array. Here is an example: ....
Originally, i was able to successfully do this using MCONCAT from
the morefunc addon. This worked great, except I am sending this
spreadsheet to others that do not have this addon installed, and it
isn't reasonable to ask them to install it, so I attempted to change
it to the aconcat UDF from Harlan Grove: ....
This results in a #NAME error. I can't seem to figure out why. . . . ....
I placed this in 'Module1' at first, then tried placing it in a sheet
module. . . .

Putting it in a general module is correct. Putting it in a worksheet
class module is incorrect.

. . . I'm not sure what else to check. This is my first attempt at
a UDF, so I appreciate any insight. I'm using Excel 2007 (as are
many of the folks that will be viewing the document), but am saving
in Excel 2003 for compatibility.

Your 1st screen capture shows the formula

=SUBSTITUTE(SUBSTITUTE(CMTS.10k.NCP.xls!Module1.aconcat
(IF('Node Counts'!$D$4:$D$999=$E7,'Node Counts'!$A$4
:$A$999),"")," FALSE,""),"FALSE","")

Is CMTS.10k.NCP.xls the file in which you're entering this formula? If
so, had you tried to define the udf before in this file?

Regardless, you may be better off commenting out all formulas that
call udfs, adding a new blank worksheet, moving all other worksheets
except this new blank one into a new workbook, and saving this new
workbook. This will effectively strip the VBA modules out of this new
workbook. Then add macro code from the original workbook into the new
workbook via copy and paste, BUT BE CAREFUL TO PASTE ONLY ONE VERSION
OF EACH UDF. Once you've copied the code to the new workbook, edit the
formulas containing udf calls to delete the workbook and module
qualifiers, e.g., in the formula above, delete

CMTS.10k.NCP.xls!Module1.

so the formula would become

=SUBSTITUTE(SUBSTITUTE(aconcat(IF('Node Counts'!$D$4:$D$999=$E7,
'Node Counts'!$A$4:$A$999),"")," FALSE,""),"FALSE","")

Better still, make this formula

=aconcat(IF('Node Counts'!$D$4:$D$999=$E7,
'Node Counts'!$A$4:$A$999,""))

 

[Ron Rosenfeld]

...

There's another potential problem. MOREFUNC is an XLL, and as long as
it's targeted at older versions too, it can't return strings longer
than 255 characters. OTOH, VBA can return strings up to 32767
characters.

Yes, that is a significant problem for using morefunc with longer strings.
--ron

 

[Josh Rogers]

Regardless, you may be better off commenting out all formulas that
call udfs, adding a new blank worksheet, moving all other worksheets
except this new blank one into a new workbook, and saving this new
workbook. This will effectively strip the VBA modules out of this new
workbook. Then add macro code from the original workbook into the new
workbook via copy and paste, BUT BE CAREFUL TO PASTE ONLY ONE VERSION
OF EACH UDF. Once you've copied the code to the new workbook, edit the
formulas containing udf calls to delete the workbook and module

This did the trick. Apparently, I had made some minor change to the
name of the function (case I think) which goofed everything up. I
renamed everything to ACAT, and it works beautifully now.

I don't suppose that Excel has some method of managing UDF's? Seeing
what is available, allowing you to 'clean' or 'wipe' them out, and
maybe entering contextual help for the UDF?

Thank you so much for your help.


-Josh