何时为Excel外接程序设置MacroOptions [英] When to set MacroOptions for Excel Addin

问题描述

我正在制作一个Excel插件.它由以下模块中的一些功能组成:

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

我将整个内容保存为.xlam Excel插件.因此，这些功能在我每次启动新的电子表格时都可用.

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

我最近了解到可以将我的功能分配给类别，这确实很有帮助.这使它们易于在Excel功能向导中使用.我使用以下代码来分配类别:

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

现在，如果我手动运行宏MyRegister，则所有函数都将获得新类别，并且效果很好.但是，我不想每次启动新电子表格时都必须手动运行宏.我的问题是，插件如何自动为每个新电子表格执行此操作?

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?

我试图像这样将其放入插件的Workbook_Open中:

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

Private Sub Workbook_Open()
    Call MyRegister
End Sub

问题是它不起作用.每当启动Excel时，我都会收到错误消息:"无法在隐藏的工作簿上编辑宏.因此，Workbook_Open事件似乎是执行此操作的错误位置.

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.

所以我的问题是，如何在适当的时候运行MyRegister宏以将我的插件函数分配给类别?

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.

谢谢！

推荐答案

您可以执行以下操作，而不是使用Workbook_Open:

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.

这篇关于何时为Excel外接程序设置MacroOptions的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！