避免在 aa 加载项 (xlam) 中重复执行应用程序级事件

Question

我有一个在工作簿的打开和关闭时工作的xlam。

为此，我使用下一个代码创建了一个类模块：

''''''''''''''''''''''' Setup Event '''''''''''''''''''''''''''''''''''''''''''''''''
Public WithEvents appevent As Application
''''''''''''''''''''''' Setup Application at Close''''''''''''''''''''''''''''''''''''''''''''
Private Sub appevent_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
MsgBox("The workbook " & Wb.Name & " will close now")
End Sub
''''''''''''''''''''''' Setup Application at Open''''''''''''''''''''''''''''''''''''''''''''
Private Sub appevent_WorkbookOpen(ByVal Wb As Workbook)
MsgBox("The workbook " & Wb.Name & " is now open")
End Sub

然后在"ThisWorkbook"对象中，我有以下代码：

Dim myobject As New Class1
Sub Workbook_Open()
Set myobject.appevent = Application
End Sub

安装外接程序后，每当文件打开时，都会出现两个消息框，一个在Excel启动时，然后在文件打开后出现一个消息框，关闭的情况类似。

为什么会发生这种情况以及如何避免这种情况？

Answer 1

未经测试，但请尝试以下操作：

Private Sub appevent_WorkbookOpen(ByVal Wb As Workbook, Cancel As Boolean)
If Wb Is ThisWorkbook Then Exit Sub  ' don't do anything when the add-in opens  
MsgBox("The workbook " & Wb.Name & " is now open")
End Sub

同样适用于appevent_WorkbookBeforeClose.