[SOLVED] When to set MacroOptions for Excel Addin

When to set MacroOptions for Excel Addin

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!

Solution

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.