How to keep reference to add-in UDF when workbook moved to different folder than add-in?

Question

I wrote an Excel add-in that provides UDFs (user-defined worksheet functions).

All is well until one user sends his workbook using those functions to another user, or just tries to use the workbook on more than one computer, where the add-in has been installed to different paths.

Even if the only difference in the paths is the drive letter, when the workbook is opened on the other computer, the old full path appears on the formulas in front of all the UDFs, and the formulas return an error.

One way of dealing with that is doing a search & replace of all formulas in the workbook, replacing the path with an empty string. Then the formulas reset themselves for the add-in's path on the current computer. Sometimes I then have to go into the VBE and run a CalculateFullRebuild to get the formulas to work. Though it works, it's a lot to ask of the less technical users, and it's annoying to have to do it frequently for those users who move their workbooks around a lot.

Also -- do COM add-ins have this problem? My add-in is an xla. Though I'm curious about that, it's a moot point in this case since COM add-ins don't work on Macintosh Excel and I need this add-in to work cross-platform.

UPDATE:

As requested, here's a screenshot:

This screenshot shows what happens if Fred, who put the add-in in C:\Fred's Stuff\Fred's Excel Stuff\MyAddin.xla, sends his workbook to Martha, who put the same add-in in another path, such as C:\Martha's Files\Martha's Excel Files\MyAddin.xla, and Martha opens the file Fred sent.

If Martha deletes the path, leaving only "=MyUDF()" in the formula, Excel will find MyAddin.xla on Martha's computer in the path where Martha put it (assuming she had previously installed MyAddin.xla as an add-in in Excel), and resolve the formula correctly.

Answer 1

This is one of the many bugbears Excel developers need to face at one time or another, and there are a few work-arounds for it, but which one you choose will depend on your own circumstances:

http://www.jkp-ads.com/articles/FixLinks2UDF00.asp

In case the link dies, here is a summary of the three suggested methods:

• Use fixed locations.
• Instead of keeping your UDF code inside the addin, you create a facility that copies the UDF routine into each workbook that uses it.
• Redirect the UDFs to the new location.