Problem distributing User Defined Function

[Paul Davenport]

I have a user defined function (called FilterCriteria) in an xla file stored
in my AddIns folder (C:\Documents and Settings\username\Application
Data\Microsoft\AddIns) and it works fine in workbooks on my PC. When I send
a workbook that uses the UDF, and the xla file to another user, it doesn't
work on their PC even when the AddIn is registered. The function shows up on
their PC as 'C:\Doc....xla'!FilterCriteria and of course they don't have that
path on their PC. The cells that use the function therefore just have a
#Name? in them.

Is there a way around this? The macros work fine in the xla, so I'm not
sure why the function doesn't. I don't want to put the xla in a seperate
fixed path on each person's PC because I wanted to use the Excel default path
for AddIns (not all our users have access to other paths on their PCs).

 

[Dave Peterson]

Have them try:
Edit|links|change source
to point at their own path/filename.

 

[Paul Davenport]

Thanks Dave. I tried this, but I get error message "Message too long". This
function is being called several times from within the same cell (I won't go
into the reasons why). So it runs out of space when the whole file path is
added to the beginning of it.

Also, these files are being shared back and forth between lots of users, all
who are not technical at all (and some who won't use them that often) so
asking them to manually do this every time is not practical.

Is this a known issue? I'm surprised if it is because the ability to share
xla files independent of the workbooks themselves seems to be the main reason
to use xla files to start with. I feel like I'm missing something but I
can't find anything on the internet about this issue.

Thanks again.

 

[Dave Peterson]

Yep. It's a common problem.

One way around it is to put the addin in a common network share. And have the
users install the addin using the UNC path--not the mapped drive letter path.
Another nice benefit is that if you have to fix something, you just update that
one file and everyone will get the update.

But if you can't do this, you could tell the users to dedicate a folder on their
C: drive for addins that you build.

C:\PaulsAddins

Then tell them to copy all the addins that they get from you into that folder
(and use the same name!).

=========
You could write code that would change the long formulas, but how would you
distribute it???? <bg>.