Excel VBA通过customUI触发宏时出现“应用程序定义或对象定义错误”

问题场景 #

在Excel工作簿中通过以下customUI配置添加功能区关闭按钮：

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui"> 
    <ribbon >
        <tabs>          
            <tab id="tab1" label="tab" visible ="true" tag ="tab1" >            
                <group id="sGroup" autoScale="true" centerVertically="true" label="bla" visible ="true" tag ="grp" >                
                    <button id="closeButton" size="large" label="close" imageMso="BroadcastEnd" tag ="close" onAction="closeCustom" 
                            visible="true" enabled="true"/>
                            
                </group>                
            </tab>          
        </tabs>     
    </ribbon>   
</customUI>

对应的VBA回调代码为：

Public Sub closeCustom(control As IRibbonControl)
    ThisWorkbook.Close False
End Sub

满足以下条件时，会弹出无错误代码的1004“应用程序定义或对象定义错误”：

• 点击customUI中的关闭按钮
• 同时打开1个及以上其他工作簿
• 当前工作簿关闭后将成为ActiveWorkbook的目标工作簿包含Workbook_Activate事件

直接通过立即窗口执行closeCustom nothing调用该函数则不会触发错误。目前仅找到application.enableevents = false的临时方案，但需用户手动重新启用事件，体验不佳。

错误复现步骤 #

1. 创建两个带宏的工作簿：book1.xlsm、book2.xlsm
2. 将book1.xlsm重命名为book1.zip
3. 打开book1.zip，添加CustomUI文件夹
4. 在CustomUI文件夹中创建customUI.xml文件，写入上述customUI配置内容
5. 修改_rels/.rels文件为以下内容：

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
    <Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties" Target="docProps/app.xml"/>
    <Relationship Id="rId2" Type="http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties" Target="docProps/core.xml"/>
    <Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/>
    <Relationship Id="rId4" Type="http://schemas.microsoft.com/office/2007/relationships/ui/extensibility" Target="CustomUI/customUI.xml"/>
</Relationships>

6. 将book1.zip重命名为book1.xlsm
7. 打开book1.xlsm，在VBA编辑器中新建模块，添加上述closeCustom回调代码
8. 打开book2.xlsm，在ThisWorkbook对象中添加以下代码：

Private Sub Workbook_Activate()
    Debug.Print "a"
End Sub

9. 保存所有文件，确保仅打开这两个工作簿
10. 点击功能区中的“tab”选项卡，再点击其中的close按钮
11. book1.xlsm关闭后弹出错误

彻底解决方法 #

方法1：延迟执行关闭操作（释放UI上下文） #

错误根源是自定义UI按钮的触发上下文与工作簿激活事件的执行上下文冲突。通过Application.OnTime将关闭操作延迟到UI线程释放后执行，即可避免冲突：

Public Sub closeCustom(control As IRibbonControl)
    ' 延迟0秒执行关闭，让UI线程先释放
    Application.OnTime Now + TimeValue("00:00:00"), "DelayedClose"
End Sub

' 单独的关闭过程，需放在标准模块中
Public Sub DelayedClose()
    ThisWorkbook.Close False
End Sub

方法2：先激活目标工作簿再关闭当前工作簿 #

手动切换到即将成为ActiveWorkbook的目标工作簿，避免关闭当前工作簿时触发其Workbook_Activate事件：

Public Sub closeCustom(control As IRibbonControl)
    Dim targetWB As Workbook
    
    ' 找到第一个非当前的工作簿
    For Each targetWB In Workbooks
        If targetWB Is Not ThisWorkbook Then
            targetWB.Activate
            Exit For
        End If
    Next targetWB
    
    ThisWorkbook.Close False
End Sub

方法3：临时禁用事件并自动恢复（优化临时方案） #

如果需要保留目标工作簿的Workbook_Activate事件触发，可以临时禁用事件，通过OnTime在关闭后自动恢复事件（注意：此方法需确保恢复事件的过程存在于不会被关闭的工作簿中，比如目标工作簿）：
在book1.xlsm的回调代码：

Public Sub closeCustom(control As IRibbonControl)
    Application.EnableEvents = False
    ' 安排在关闭后恢复事件
    Application.OnTime Now + TimeValue("00:00:01"), "Book2.xlsm!EnableEventsAgain"
    ThisWorkbook.Close False
End Sub

在book2.xlsm的标准模块中添加：

Public Sub EnableEventsAgain()
    Application.EnableEvents = True
    ' 手动触发激活事件（可选，根据需求）
    Debug.Print "a"
End Sub

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