当工作簿移动到与加载项不同的文件夹时，如何保留对加载项UDF的引用? [英] How to keep reference to add-in UDF when workbook moved to different folder than add-in?

问题描述

我写了一个提供UDF(用户定义的工作表函数)的Excel加载项.

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前面的公式中，并且这些公式会返回错误.

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.

一种解决方法是进行搜索&替换工作簿中的所有公式，将路径替换为空字符串.然后，公式将自己重置为当前计算机上加载项的路径.有时，然后我必须进入VBE并运行CalculateFullRebuild才能使公式起作用.尽管它可以工作，但对于技术含量较低的用户来说却有很多要求，对于那些经常移动工作簿的用户来说，经常这样做是很烦人的.

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.

而且-COM加载项是否有此问题?我的外接程序是xla.尽管我对此感到很好奇，但是在这种情况下这是一个有争议的问题，因为COM加载项在Macintosh Excel上不起作用，并且我需要此加载项才能跨平台工作.

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.

更新:

根据要求，以下是屏幕截图:

As requested, here's a screenshot:

此屏幕截图显示了将加载项放入C:\ Fred's Stuff \ Fred's Excel Stuff \ MyAddin.xla的Fred将他的工作簿发送给Martha的情况，后者将相同的加载项放入了另一条路径，例如作为C:\ Martha的Files \ Martha的Excel Files \ MyAddin.xla，Martha打开Fred发送的文件.

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.

如果Martha删除路径，仅在公式中保留"= MyUDF()"，Excel将在Martha放置路径的路径中在Martha的计算机上找到MyAddin.xla(假设她以前已将MyAddin.xla作为附加项安装.在Excel中输入)，然后正确解析公式.

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.

推荐答案

这是Excel开发人员一次或多次需要面对的众多错误之一，并且有一些解决方法，但是您可以选择哪一个选择将取决于您自己的情况:

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:

• 使用固定位置.
• 创建UDF例程而不是将UDF代码保留在插件中，而是将UDF例程复制到使用该例程的每个工作簿中.
• 将UDF重定向到新位置.

这篇关于当工作簿移动到与加载项不同的文件夹时，如何保留对加载项UDF的引用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！