Register UDF with descriptions of arguments using excel addin

Question

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

in IDE, when you select AddIn `ThisWorkbook module`, next goto Properties Window, search for `Is Addin` property and change it to `False` to see the Workbook. However, I'm not sure if this is your problem. — Kazimierz Jawor, Jan 04 '17 at 14:08
@KazimierzJawor Thanks for suggestion. It results in displaying the addin as regular workbook, with some visible sheet, but then it can no longer be saved as an addin file type. I want this to be shared as an addin. — kolcinx, Jan 04 '17 at 14:15
you are wrong, it can be saved as AddIn. All you need to do is to change back this `IsAddin`property to `True` after you make any changes. — Kazimierz Jawor, Jan 04 '17 at 14:22
@KazimierzJawor I see, you are proposing an temporary state. Will try that. — kolcinx, Jan 04 '17 at 14:33
@KazimierzJawor Hi, can you please help with my issue in Edit #1? — kolcinx, Jan 04 '17 at 17:29
I don't get the issue with remaining 'addin window'. Your code seems to be quite ok. Maybe the problem is inside `getRegExResultRegister` subroutine. — Kazimierz Jawor, Jan 05 '17 at 06:14
Raised by @ScottTrautmann: I get the exact same behavior when using the code given in Edit #1. Has anyone resolved this issue? — Ralf Stubner, Jun 22 '18 at 14:33

Vincent G · Answer 1 · 2017-01-04T15:50:55.247

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

Can you please recommend, where to put this lines? Inside some event or into UDF itself, or where? — kolcinx, Jan 04 '17 at 15:31
Either inside the Workbook_Open before the `Call getRegExResultRegister` or at the start of the `getRegExResultRegister` function itself. — Vincent G, Jan 04 '17 at 15:48
When I use this code, it immediately stops, when it executes the `Application.AddIns("Your Addin name").Installed = False`, since this gets rid of the addin that is currently running the code. :) — kolcinx, Jan 04 '17 at 17:31

Register UDF with descriptions of arguments using excel addin

Question 1

Question 2

Edit #1

1 Answers1

Linked