如何让 Excel 加载项 (Excel 2007) 中的用户定义函数与自动完成功能配合使用？

发布于 2024-10-02 00:30:58 字数 122 浏览 8 评论 0原文

我创建了一个加载项，当我在表达式框中使用它时，自动完成功能不起作用。当我单击用户定义函数下的函数“f”按钮时，我确实看到了它们。我只是希望自动完成功能能够与它们一起使用，这样我就不必记住它们的名字，也不必每次都单击功能“f”按钮。

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

水波映月 2024-10-09 00:30:58

尽管这是一个旧线程，但似乎解决方案很少。我在 JKP 应用程序开发服务中找到了一个示例，最初由 Laurent Longre 发现。一项警告解释如下：

这种技巧方法的缺点是，实际上是在使用的 dll 中重新注册一个函数，该函数可能被任何程序使用

http://www.jkp-ads.com/Articles/RegisterUDF01.asp

此解决方案仅注册/取消注册 UDF，但用户仍然需要保存工作簿作为 .xlam 并安装插件。我使用以下代码自动将当前工作簿安装为 Excel 插件（如果您要更新插件，则需要添加一些错误捕获以确定插件是否已安装）。

'Saves current workbook as an .xlam file
sFile = Application.LibraryPath & "\" & "name_of_addin" & ".xlam"
ThisWorkbook.SaveAs sFile, 55
ThisWorkbook.IsAddin = True
'Adds temporary workbook
Workbooks.Add
'Installs the addin
Set oAddin = AddIns.Add(sFile , False)
oAddin.Installed = True
'Closes temporary workbook
Workbooks(Workbooks.Count).Close
MsgBox ("Installation Successful.  Please close Excel and restart.")
'Closes workbook without saving
Workbooks(sFirstFile).Close False

Although this is an old thread, there seems to be very little solutions out there. I have found an example over at JKP Application Development Services originally found by Laurent Longre. One caveat is explained below:

Disadvantage of this trick method, is that one is actually re-registering a function within the dll one uses, which might be used by any program

http://www.jkp-ads.com/Articles/RegisterUDF01.asp

This solution only registers/un-registers the UDF, but the user will still have to save the workbook as an .xlam and install the addin. I used the following code to automatically install the current workbook as an Excel addin (if you are going to be updating the addin, you'll need to add some error catching to determine if the addin is already installed).

'Saves current workbook as an .xlam file
sFile = Application.LibraryPath & "\" & "name_of_addin" & ".xlam"
ThisWorkbook.SaveAs sFile, 55
ThisWorkbook.IsAddin = True
'Adds temporary workbook
Workbooks.Add
'Installs the addin
Set oAddin = AddIns.Add(sFile , False)
oAddin.Installed = True
'Closes temporary workbook
Workbooks(Workbooks.Count).Close
MsgBox ("Installation Successful.  Please close Excel and restart.")
'Closes workbook without saving
Workbooks(sFirstFile).Close False

回复收藏 0 原文