如何让Excel加载项自动运行宏（Office2021+macOS环境）

环境 #

Office 2021、Excel 16.79.2（最新版）、VBA 7.1、macOS Sonoma 14.1.2

问题描述 #

通过VB编辑器引用关联到多个.xlsm工作簿的.xlam加载项，需要自动初始化公共Collection变量myColl，但加载项的Workbook_Open子程序未触发——原因是引用加载项不会“打开”该工作簿，因此该事件不生效。

可行方法 #

方法1：使用Auto_Open宏（最简方案） #

加载项被加载时（首次打开引用它的工作簿时），Auto_Open会自动运行，可在此完成集合初始化，同时添加判断避免重复操作：

' 在加载项的标准模块中声明公共变量
Public myColl As Collection

Sub Auto_Open()
    ' 仅当集合未初始化时执行
    If myColl Is Nothing Then
        Set myColl = New Collection
        ' 填充集合的示例代码
        myColl.Add "数据项1"
        myColl.Add "数据项2"
        ' 自定义填充逻辑...
    End If
End Sub

注意：后续打开其他引用该加载项的工作簿时，加载项已处于加载状态，Auto_Open不会重复运行，但myColl作为公共变量会保留初始化结果，符合需求。

方法2：捕获Application级工作簿打开事件（更灵活） #

如果需要在每个关联工作簿打开时执行初始化（或额外操作），可通过类模块捕获Application的WorkbookOpen事件：

1. 在加载项中插入类模块，命名为clsAppEvents，写入以下代码：

Option Explicit
WithEvents app As Excel.Application

Private Sub app_WorkbookOpen(ByVal Wb As Workbook)
    ' 判断当前打开的工作簿是否引用了本加载项
    Dim ref As Reference
    Dim isReferenced As Boolean
    isReferenced = False
    
    For Each ref In Wb.VBProject.References
        If ref.Name = ThisWorkbook.VBProject.Name Then
            isReferenced = True
            Exit For
        End If
    Next ref
    
    ' 仅当工作簿引用本加载项时，初始化集合
    If isReferenced Then
        If myColl Is Nothing Then
            Set myColl = New Collection
            ' 填充集合的代码
            myColl.Add "数据项1"
            ' 自定义逻辑...
        End If
    End If
End Sub

2. 在加载项的标准模块中添加事件监听初始化代码：

Public myColl As Collection
Public appEventMonitor As clsAppEvents

Sub Auto_Open()
    Set appEventMonitor = New clsAppEvents
    Set appEventMonitor.app = Application
End Sub

Sub Auto_Close()
    ' 退出时释放对象
    Set appEventMonitor = Nothing
    Set myColl = Nothing
End Sub

注意事项 #

• 确保Excel信任中心设置允许加载项和宏运行，否则代码无法触发。
• 在macOS环境下，加载项建议放置在Excel认可的加载项目录（如~/Library/Group Containers/UBF8T346G9.Office/User Content/Startup/），或通过“开发工具>加载项”手动添加，确保加载项能被正确加载。

内容的提问来源于stack exchange，提问作者nunof34