Excel插件UDF路径问题

Any suggestions on best practice for dealing with this issue?

对于处理这个问题的最佳实践有什么建议吗?

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.

我编写了一个提供udf(用户定义的工作表函数)的Excel外接程序。直到一个用户使用这些功能向另一个用户发送他的工作簿，或者仅仅尝试在多个计算机上使用工作簿，在这些计算机上插件已经安装到不同的路径。

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.

即使路径上唯一的区别是驱动器号，当在另一台计算机上打开工作簿时，旧的完整路径出现在所有udf前面的公式中，公式返回一个错误。

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.

解决这个问题的一种方法是对工作簿中的所有公式进行搜索和替换，用空字符串替换路径。然后，公式为当前计算机上的外接程序路径重置自身。有时，我必须进入VBE并运行一个calculatlerebuild以使公式生效。虽然它很有用，但是对技术不太熟练的用户来说，这是一个很大的问题，而且对于那些经常移动他们的工作簿的用户来说，频繁地这样做是很烦人的。

Suggestions?

建议吗?

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.

COM插件也有这个问题吗?我的外接程序是xla。尽管我对此很好奇，但这在这个案例中是没有意义的，因为COM外接程序不能在Macintosh Excel上工作，我需要这个外接程序在跨平台上工作。

UPDATE:

更新:

As requested, here's a screenshot:

按照要求，这里有一个截屏:

Screenshot showing path in formula http://i62.tinypic.com/axz68n.png

显示公式http://i62.tinypic.com/axz68n.png路径的截图

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.

这个屏幕截图显示了如果弗雷德，谁把插件放在C:\弗雷德的东西\弗雷德的Excel东西\MyAddin。xla将自己的工作簿发送给Martha, Martha将相同的附加组件放在另一个路径中，比如C:\Martha的文件\Martha的Excel文件\MyAddin。xla，玛莎打开弗雷德发送的文件。

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.

如果Martha删除了路径，在公式中只留下“=MyUDF()”，Excel就会找到MyAddin。xla在Martha的电脑上的路径(假设她之前安装了MyAddin)。xla作为Excel中的外接程序)，并正确解析公式。

1 个解决方案

#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:

这是Excel开发人员需要面对的众多问题之一，有一些解决方法，但是您选择哪一个取决于您自己的环境:

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.
不要将UDF代码保存在插件中，而是创建一个工具，将UDF例程复制到使用它的每个工作簿中。
Redirect the UDFs to the new location.
将udf重定向到新位置。

#1