When to set MacroOptions for Excel Addin

Question

I'm making an Excel addin. It consists of a few functions in a module like this:

Public Function MyFunctionOne(X As Range, Y As Double) As Double
    MyFunctionOne = 1 'Example
End Function
Public Function MyFunctionTwo(X As Range, Y As Double) As Double
    MyFunctionTwo =  2 'Example
End Function
Public Function MyFunctionThree(X As Range, Y As Double) As Double
    MyFunctionThree =  3 'Example
End Function

I've saved the whole thing as a .xlam Excel Addin. So those functions are available every-time I start a new spread sheet.

I recently learned that I can assign my functions to a category, which is really helpful. This makes them easy to use from the Excel function wizard. I use the following code to assign categories:

Public Sub MyRegister()
    Application.MacroOptions Macro:="MyFunctionOne", Description:="Returns 1", Category:="My New Category"
    Application.MacroOptions Macro:="MyFunctionTwo", Description:="Returns 2", Category:="My New Category"
    Application.MacroOptions Macro:="MyFunctionThree", Description:="Returns 3", Category:="My New Category"
End Sub

Now if I manually run the macro, MyRegister, the functions all get the new category and it works very well. But I don't want to have to manually run the macro each time I start a new spreadsheet. My questions is, how can the addin do this automatically for each new spreadsheet?

I tried putting it in the Workbook_Open of the addin like this:

Private Sub Workbook_Open()
    Call MyRegister
End Sub

The problem is that it doesn't work. Whenever Excel starts, I get the error message: "Cannot edit a macro on a hidden workbook." So the Workbook_Open event seems to be the wrong place to do this.

So my question is, how do I run the MyRegister macro at the proper time to assign my addin functions to categories?

By the way, I really don't want to make a template. I really to keep this as only and addin.

Thanks!

Answer 1

Put RegisterUDF() in a private module, you can then call it from WorkbookOpen() and the error

cannot edit a macro in a hidden workbook

will not be generated.

Answer 2

At the time of my writing this, the question is quite old (going on 5 years), yet this question provided me with the information I needed to completely fix this issue. I was able to take the answer provided by @jerryact and expand upon it to fix my issue.

To begin with, similar to the original poster, I had my UDF registered in the Workbook_Open event, which when loaded as an Add-In, cause the Run-time error '1004': Cannot edit a macro on a hidden workbook. Unhide the workbook using the Unhide command. error message to be displayed when opening Excel.

Using the information in the answer above by @jerryact, I changed my code to look like the following:

Option Explicit

Private WithEvents App As Application

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    modUDFs.UnregisterUDF "DMS_2_DD"
    modUDFs.UnregisterUDF "DD_2_DMS"
    modUDFs.UnregisterUDF "ElapsedTime"
End Sub

Private Sub Workbook_Open()
    Set App = Application
End Sub

Private Sub App_WorkbookActivate(ByVal Wb As Workbook)
    modUDFs.RegisterUDF "Converts Decimal Degrees to Degrees, Minutes, Seconds" & vbLf & "rbcDD_2_DMS(<Decimal Degrees>)", "DD_2_DMS", 3
    modUDFs.RegisterUDF "Converts Degrees, Minutes, Seconds to Decimal Degrees" & vbLf & "rbcDMS_2_DD(<Degrees Minutes Seconds>)", "DMS_2_DD", 3
    modUDFs.RegisterUDF "Calculates the Time between two time stamps" & vbLf & _
                        "rbcElapsedTime(<end time>,<start time>,<output code>)" & vbLf & _
                        "0 = <Seconds>,  1 = <Minutes:Seconds>" & vbLf & _
                        "2 = <Hours:Minutes:Seconds>,  3 = <Days Hours:Minutes:Seconds>", "ElapsedTime", 2
End Sub

This worked great and appeared to fix my problem (and did 99.9% of the time). However, understanding that my Add-In could be unloaded by the user at any time, I wanted to make sure that when my Add-In was unloaded, that the UDFs that it provided and registered, were unregistered. This meant that the solution provided by @jerryact would cause the same error message as before but only in the specific circumstance where the user opened Excel but then closed the application before actually opening a workbook or creating a blank one. This lead me to my final solution which is shown here:

Option Explicit

Private WithEvents App As Application

Private Sub App_WindowDeactivate(ByVal Wb As Workbook, ByVal Wn As Window)
    modUDFs.UnregisterUDF "DMS_2_DD"
    modUDFs.UnregisterUDF "DD_2_DMS"
    modUDFs.UnregisterUDF "ElapsedTime"
End Sub

Private Sub Workbook_Open()
    Set App = Application
End Sub

Private Sub App_WorkbookActivate(ByVal Wb As Workbook)
    modUDFs.RegisterUDF "Converts Decimal Degrees to Degrees, Minutes, Seconds" & vbLf & "rbcDD_2_DMS(<Decimal Degrees>)", "DD_2_DMS", 3
    modUDFs.RegisterUDF "Converts Degrees, Minutes, Seconds to Decimal Degrees" & vbLf & "rbcDMS_2_DD(<Degrees Minutes Seconds>)", "DMS_2_DD", 3
    modUDFs.RegisterUDF "Calculates the Time between two time stamps" & vbLf & _
                        "rbcElapsedTime(<end time>,<start time>,<output code>)" & vbLf & _
                        "0 = <Seconds>,  1 = <Minutes:Seconds>" & vbLf & _
                        "2 = <Hours:Minutes:Seconds>,  3 = <Days Hours:Minutes:Seconds>", "ElapsedTime", 2
End Sub

Notice that the Workbook_BeforeClose event is gone and I've moved my unregister calls to the App_WindowDeactivate event. This fixed both issues and gives me an Add-In which can register and unregister my UDF's. All of the above code was placed in the ThisWorkbook and the below code, which handles the registering and unregistering of the UDFs was placed in my modUDFs code module

Sub RegisterUDF(ByVal fDescription As String, ByVal fUDFName As String, ByVal fCategory As Variant)
    'Integer Category
    '1   Financial
    '2   Date & Time
    '3   Math & Trig
    '4   Statistical
    '5   Lookup & Reference
    '6   Database
    '7   Text
    '8   Logical
    '9   Information
    '10  Commands
    '11  Customizing
    '12  Macro control
    '13  DDE/External
    '14  User Defined
    '15  First custom category
    '16  Second custom category
    '17  Third custom category
    '18  Fourth custom category
    '19  Fifth custom category
    '20  Sixth custom category
    '21  Seventh custom category
    '22  Eighth custom category
    '23  Ninth custom category
    '24  Tenth custom category
    '25  Eleventh custom category
    '26  Twelfth custom category
    '27  Thirteenth custom category
    '28  Fourteenth custom category
    '29  Fifteenth custom category
    '30  Sixteenth custom category
    '31  Seventeenth custom category
    '32  Eighteenth custom category
    
    If IsNull(fCategory) Then fCategory = 9
    If fCategory = "" Then fCategory = 9
    Application.MacroOptions Macro:=fUDFName, Description:=fDescription, Category:=fCategory
End Sub

Sub UnregisterUDF(ByVal fUDFName As String)
    Application.MacroOptions Macro:=fUDFName, Description:=Empty, Category:=Empty
End Sub

Answer 3

Instead of using Workbook_Open, you can do this:

Private WithEvents App As Application

Private Sub App_WorkbookActivate(ByVal Wb As Workbook)
    MyRegister
End Sub

Private Sub Workbook_Open()
    Set App = Application
End Sub

That way it'll run when a workbook is active, and you'll avoid the error you're getting.

Answer 4

Almost there. Just convert the add-in as normal workbook, set options and reset as add-in. See code comments for details.

Public Sub MyRegister()
    Application.ScreenUpdating = False '/ Turn it off to avoid flicker.
    ThisWorkbook.IsAddin = False '/ Make the add-in workbook as normal, hence unhiding sheets
    Application.MacroOptions Macro:="MyFunctionOne", Description:="Returns 1", Category:="My New Category"
    Application.MacroOptions Macro:="MyFunctionTwo", Description:="Returns 2", Category:="My New Category"
    Application.MacroOptions Macro:="MyFunctionThree", Description:="Returns 3", Category:="My New Category"
    ThisWorkbook.IsAddin = True '/ Set back as add-in, hides everything.
    Application.ScreenUpdating = True '/ Turn on screen updating
End Sub