5

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:

Screenshot showing path in formula

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.

Community
  • 1
  • 1
Greg Lovern
  • 868
  • 2
  • 18
  • 30
  • Greg, use the UNC path rather than the drive "letter". That should take care of it. – David Zemens Sep 08 '14 at 14:18
  • Can you screenshot some examples ... ? – John Alexiou Sep 08 '14 at 17:04
  • @David Zemens: How would I do that? Neither the developer nor the user ever enters the path to the add-in at all. Excel does that all on its own. Are you saying that when the user enters a formula, they should include the UNC path to the add-in in the formula, once for each instance of any UDF in the formula? That would be an absurd requirement. – Greg Lovern Sep 08 '14 at 17:37
  • @ja72: Do you mean a screenshot of Excel's formula bar showing full paths to the add-in for each instance of a UDF in a formula? How would that help? If not, what exactly do you want a screenshot of? – Greg Lovern Sep 08 '14 at 17:41
  • I was under the impression that the UDF itself was the source were the *source* of the bad file paths, in which case I recommended just using the UNC but if the "bad" file paths are happening because Excel automatically inserts the full path of other open workbooks used in formula reference, and you do not enforce drive letter naming rules (e.g., the same drive is mapped as "T" for you but "Z" for me, etc.) then I'd have to think about this for a minute... Seems to me that I have an add-in that replaces broken links though, could probably be used to fix these for you. – David Zemens Sep 08 '14 at 18:21
  • 1
    It might be useful to show a screenshot of one or two of the formulas. – David Zemens Sep 08 '14 at 18:23
  • @David Zemens: I've never heard of a case where the UDF was itself the source of that path, and I don't know where in the UDF itself I'd put that path. Since you're answering this question, surely you know that the UDF is just a function in a VBA code module in the add-in workbook. I also don't know where I'd put the path in the add-in workbook, if that's what you mean. When the user adds the add-in using the add-ins dialog, Excel enters the path to the add-in in the registry. – Greg Lovern Sep 08 '14 at 21:56
  • @David Zemens: Also, even if the path were UNC, how would that help? When one user emails his workbook to another, their paths to the add-in may be different in more ways that just the drive letter. Suppose one puts it in C:\Fred\MyAddIn.xla, and another person puts it in C:\Martha\MyAddIn.xla. So Fred changes his to a UNC path that resolves to C:\Fred\MyAddIn.xla. How does that help Martha? – Greg Lovern Sep 08 '14 at 22:06
  • I've added a screenshot above as requested. – Greg Lovern Sep 08 '14 at 23:24
  • 1
    Possible duplicate of http://stackoverflow.com/questions/6414719/remove-addin-path-from-udf-in-excel-formula. Anyways, looking at the link, you will understand, that only via Add-In, no solution is available. – cyboashu Sep 09 '14 at 02:59
  • @cyboashu: Thanks, but I had read that and it doesn't look like the same problem. The issue in that case is migrating the UDFs from an Excel .xla workbook add-in to an ExcelDNA .NET DLL, even with the same path. Excel stores different information for .xla vs. DLL, so affected formulas have to be reentered. Different issue than just different path to the same .xla add-in on different computers. – Greg Lovern Sep 09 '14 at 06:30

1 Answers1

5

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.
RyanfaeScotland
  • 1,128
  • 9
  • 28
Tim
  • 696
  • 1
  • 7
  • 12
  • 1
    Thanks, after a quick scan I think the last item listed there, "Redirect the UDFs to the new location", looks good, except for the comment "We assume the function is on its own, NOT nested inside another!!!". That's bad news which makes the solution useless in my case and possibly confusing for some users (and therefore worse than nothing), since it would fix some paths and not others, and possibly mangle some. I'll have to see if I can get it to work with nested functions. Even if I can't though, it's good to know that I hadn't been missing a really good solution all along. – Greg Lovern Sep 09 '14 at 06:38
  • 2
    @Tim - I added a little summary of what the link contains so as to avoid a near link only answer and hopefully make your answer a bit more resilient to time. Please feel free to adjust if you feel like I'm putting words in your mouth. – RyanfaeScotland Jan 28 '16 at 09:26