使用excel addin注册UDF和参数说明 [英] Register UDF with descriptions of arguments using excel addin

问题描述

我有一个加载UDF getRegExResult 的插件。我想为这个函数添加一个函数描述和参数描述，所以当用户安装addin时，关闭，打开excel几次，并进入Insert Function对话框，他将能够找到具有参数描述的函数。

同样的问题是在这里。我发现一个符合我需求的回答。除了...

我想通过Excel Addin来做到这一点。我的想法是调用addin workbook_open 事件如下：

  Private Sub Workbook_Open（）
调用getRegExResultRegister 
 End Sub 
 
 Public Sub getRegExResultRegister（）
 Application.MacroOptions Macro：=getRegExResult，描述：=返回连接的NONE，ONE或ALL正则表达式匹配的字符串，类别：=用户定义，_ 
 ArgumentDescriptions：= Array（用于检查匹配的源字符串，_ 
正则表达式模式，例如\d +至少匹配一个或多个数字，_ 
[Default = True] True =返回所有匹配的结果False =仅返回第一个匹配。，_ 
[Default = True] True =不区分大小写的搜索。False =区分大小写的搜索。，_ 
[Default =;]在每个macth之间插入的分隔符如果发现多于1个匹配项。）
 End Sub 
  

安装完加入，关闭，打开excel，我得到运行时错误1004：无法编辑隐藏的工作簿上的宏。我们试图把它放在工作簿中...

问题1

Thisworkbook.Windows（1）.visible = True 进入注册前的 Workbook_open 事件，但导致运行时9，下标超出范围。

问题2

如果取消隐藏加载项是不可能的，是否有另一种方法是这样做？

---

感谢您的帮助。

类似的问题：

在Personal.xslb中注册UDF

编辑＃1

当前代码做我想要的，有一个错误，当我打开一些现在的工作簿，我得到2个excel窗口其中一个打开的工作簿（正确的），其中一个addin（不想要的）如何摆脱第二个窗口？

  Private Sub Workbook_Open（）
 With ThisWorkbook 
 .IsAddin = False 
调用getRegExResultRegister 
 .IsAddin = True 
 .Saved = True 
结束
 End Sub 
  / pre> 
 
解决方案
在设置.MacroOption之前，请使用以下代码：
  Application.AddIns（您的Addin名称）Installed = True 
  
此代码可能需要在之前：
  Application.AddIns（您的Addin名称）Installed = False 
  
根据 MSDN Blog ，这是因为启动时自动加载的AddIns并没有真正打开。所以你必须先关闭它，然后重新打开它。
 
 
 
请注意，你的Addin名称不是AddIn的文件名，它的名称显示在加载项选项窗口中。
 
 
 
 编辑：完整的代码，不要忘记编辑AddIn名称 
  Public Sub getRegExResultRegister（）
 Application.AddIns（Your Addin name）Installed = False 
 Application.AddIns（Your Addin name）Installed = True 
 Application.MacroOptions Macro：=getRegExResult，描述：=返回NONE，ONE或ALL的连接字符串正则表达式匹配（），类别：=用户定义，_ 
参数描述：= Array（用于检查匹配的源字符串，_ 
正则表达式模式例如\ d +至少匹配一个或多个数字。，_ 
[Default = True] True =返回所有找到的匹配项。False =仅返回第一个匹配项。，_ 
[默认= True] True =不是case sens真正的搜索。 False =区分大小写搜索，_ 
[Default =;]]在每个macth之间插入分隔符，如果找到多于1个匹配项。）
 End Sub 
  
 
I have an addin with an UDF getRegExResult. I want to add a function description and arguments descriptions to this function, so when user installs the addin, closes, opens excel few times and goes to "Insert Function" Dialog box he will be able to find the function with description of the arguments.  

The same is asked here. I found one answer that suits my needs. Except...  

I want to be able to do this through an Excel Addin. My idea is to put call into addin workbook_open event like so:  
Private Sub Workbook_Open()
    Call getRegExResultRegister
End Sub  

Public Sub getRegExResultRegister()
    Application.MacroOptions Macro:="getRegExResult", Description:="Returns a concatenated string of NONE, ONE, or ALL Regular Expression Match(es).", Category:="User Defined", _
        ArgumentDescriptions:=Array("Source string to inspect for matches.", _
        "Regular Expression Pattern. E.g. ""\d+"" matches at least 1 or more digits.", _
        "[Default = True] True = Returns all the matches found. False = Returns only the first match.", _
        "[Default = True] True = Not case sensitive search. False = Case sensitive search.", _
        "[Default = "";""] Delimiter to insert between every macth, if more than 1 matches are found.")
End Sub
After I install the addin, close, open excel, I get runtime error 1004: "Cannot edit a macro on a hidden workbook. Uhnide the workbook..."  

Question 1

How to unhide an addin workbook? I tried to put Thisworkbook.Windows(1).visible = True into the Workbook_open event before the call to register, but that results in Runtime 9, subscript out of range.

Question 2

If the unhide addin is impossible, is there any other way to do this?



Thanks for help.

Similar questions:

Excel Register UDF in Personal.xslb  

Edit #1

Current code does what I want, with one bug. When I open some existing workbook, I get 2 excel windows. One of the opened workbook (correct), one of the addin (not wanted). How to get rid of the second window?   
Private Sub Workbook_Open()
    With ThisWorkbook
        .IsAddin = False
        Call getRegExResultRegister
        .IsAddin = True
        .Saved = True
    End With
End Sub

解决方案
Use the following code before setting the .MacroOption:
Application.AddIns("Your Addin name").Installed = True
This code may need to be preceded by :
Application.AddIns("Your Addin name").Installed = False
According to MSDN Blog, it is because automation loaded AddIns are not really opened at startup. So you have to close it before re-openning it.

Note that "Your Addin name" is not the filename of the AddIn but its Name as it appears in the Add-ins option windows.

Edit: Full code, don't forget to edit the AddIn name
Public Sub getRegExResultRegister()
    Application.AddIns("Your Addin name").Installed = False
    Application.AddIns("Your Addin name").Installed = True
    Application.MacroOptions Macro:="getRegExResult", Description:="Returns a concatenated string of NONE, ONE, or ALL Regular Expression Match(es).", Category:="User Defined", _
        ArgumentDescriptions:=Array("Source string to inspect for matches.", _
        "Regular Expression Pattern. E.g. ""\d+"" matches at least 1 or more digits.", _
        "[Default = True] True = Returns all the matches found. False = Returns only the first match.", _
        "[Default = True] True = Not case sensitive search. False = Case sensitive search.", _
        "[Default = "";""] Delimiter to insert between every macth, if more than 1 matches are found.")
End Sub


                        
这篇关于使用excel addin注册UDF和参数说明的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！