关闭原工作簿时跨工作簿运行Excel Macro的异常问题排查

我有两个测试工作簿Red.xlsm和Blue.xlsm，需要在Red中运行宏实现以下流程：

• 打开Blue.xlsm
• 关闭Red.xlsm
• 在Blue中执行操作
• 重新打开Red.xlsm并在其中执行操作

目前能在Red中打开Blue并调用Blue的宏，但当Blue的宏关闭Red后，宏就停止运行，无法完成后续重新打开Red并操作的步骤。测试后发现，只有Macro2的第一步在Red中执行成功，后续步骤都没运行，最终仅Blue处于打开状态。

现有代码 #

Red.xlsm中的Macro1 #

Sub Macro1()
    Workbooks.Open "C:\Users\Carey.Zimmer\Documents\Practice\September 2022\BookHopping\Blue.xlsm"
    Range("B2") = "Macro 1 Run Successfully in Blue"
    Workbooks("Red.xlsm").Activate
    Range("B2") = "Macro 1 Run Successfully in Red"
    Application.Run ("'Blue.xlsm'!Macro2")
End Sub

Blue.xlsm中的Macro2 #

Sub Macro2()
    Workbooks("Red.xlsm").Activate
    Range("B3") = "Macro 2 Step 1 Run Successfully in Red"
    Workbooks("Blue.xlsm").Activate
    Workbooks("Red.xlsm").Close SaveChanges:=True
    Workbooks("Blue.xlsm").Activate
    Range("B3") = "Macro 2 Run Successfully in Blue"
    Workbooks.Open ("C:\Users\Carey.Zimmer\Documents\Practice\September 2022\BookHopping\Red.xlsm")
    Range("B4") = "Macro 2 Step 2 Run Successfully in Red"
End Sub

问题原因 #

Macro1是在Red.xlsm的VBA项目中启动执行的，当Macro2关闭Red.xlsm时，承载Macro1的VBA项目会被Excel卸载，导致整个调用链中断——原本Macro1调用Macro2，Red关闭后，Macro2的后续代码失去了执行上下文，自然无法继续运行。

解决方案 #

把整个流程的控制权转移到Blue.xlsm的宏中，让Red的宏仅负责触发Blue的宏并传递必要信息，确保后续所有操作都在Blue的VBA上下文里执行。

修改后的代码 #

Red.xlsm中的Macro1

Sub Macro1()
    Dim blueWB As Workbook
    ' 打开Blue并获取工作簿对象
    Set blueWB = Workbooks.Open("C:\Users\Carey.Zimmer\Documents\Practice\September 2022\BookHopping\Blue.xlsm")
    
    ' 在Blue中执行初始操作
    blueWB.Sheets(1).Range("B2") = "Macro 1 Run Successfully in Blue"
    ' 在Red中执行初始操作
    ThisWorkbook.Sheets(1).Range("B2") = "Macro 1 Run Successfully in Red"
    
    ' 调用Blue的宏，传入Red的文件路径，避免硬编码
    Application.Run "'" & blueWB.Name & "'!CompleteWorkflow", ThisWorkbook.FullName
End Sub

Blue.xlsm中的CompleteWorkflow宏

Sub CompleteWorkflow(redFilePath As String)
    Dim redWB As Workbook
    
    ' 第一步：在Red中执行操作
    Set redWB = Workbooks("Red.xlsm")
    redWB.Sheets(1).Range("B3") = "Macro 2 Step 1 Run Successfully in Red"
    
    ' 关闭Red并保存
    redWB.Close SaveChanges:=True
    
    ' 在Blue中执行操作
    ThisWorkbook.Sheets(1).Range("B3") = "Macro 2 Run Successfully in Blue"
    
    ' 重新打开Red
    Set redWB = Workbooks.Open(redFilePath)
    ' 在Red中执行后续操作
    redWB.Sheets(1).Range("B4") = "Macro 2 Step 2 Run Successfully in Red"
End Sub

说明 #

• 用ThisWorkbook替代硬编码的工作簿名称，避免因文件名变化导致代码出错
• 将流程逻辑全部放在Blue的宏中，确保Red关闭后，代码仍在Blue的VBA上下文里执行，不会中断
• 通过参数传递Red的文件路径，减少硬编码，提升代码可维护性

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