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!
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.