关闭另一工作簿时触发无限循环的Excel VBA问题求助

场景说明 #

开发Excel VBA项目时涉及两个工作簿：

• 工作簿A：打开时自动执行宏更新分析请求表格，随后调用my_copy过程
• 工作簿B：被my_copy打开，完成数据复制补充操作后需要关闭

现有代码 #

工作簿A的启动宏代码 #

ThisWorkbook.Activate
Set myCurrentWorkbook = ThisWorkbook

' 执行更新表格的操作
do some stuffs...

Call my_copy

my_copy过程代码 #

Set myWorkbook = Workbooks.Open(myPath)
myWorkbook.Worksheets(mySheet).Select

' 执行数据复制和补充操作
do some stuff

myWorkbook.Close SaveChanges:=True

尝试过的无效修复代码 #

myWorkbook.Save
myWorkbook.Close (True)

问题现象 #

关闭工作簿B时Excel直接崩溃重启，重启后工作簿A的启动宏会再次触发，循环调用my_copy导致无限崩溃循环；仅执行myWorkbook.Save不关闭时无崩溃问题。

问题根源及解决方法 #

可能的根源 #

1. Select操作引发对象冲突：代码依赖Select激活工作表，关闭工作簿时Excel内部对象引用混乱，触发崩溃。
2. 崩溃重启的重复触发：Excel崩溃后会自动重新打开崩溃时的所有工作簿，导致工作簿A的Workbook_Open事件重复执行。
3. 事件交互冲突：工作簿B可能存在未处理的Workbook_BeforeClose等事件，与工作簿A的宏逻辑产生冲突。
4. 对象引用未释放：打开的工作簿对象未正确释放，导致内存引用异常。

具体解决步骤 #

1. 移除不必要的Select操作：VBA应尽量避免依赖Select/Activate，直接通过对象引用操作工作表：

   Sub my_copy()
       Dim myWorkbook As Workbook
       Dim ws As Worksheet
       
       Set myWorkbook = Workbooks.Open(myPath)
       Set ws = myWorkbook.Worksheets(mySheet)
       
       ' 后续直接用ws对象操作，比如 ws.Range("A1").Value = "xxx"
       do some stuff
       
       myWorkbook.Close SaveChanges:=True
       Set ws = Nothing
       Set myWorkbook = Nothing
   End Sub
   

2. 添加防重复执行逻辑：在工作簿A的Workbook_Open事件中加入标记，避免崩溃重启后重复触发宏：

   Private Sub Workbook_Open()
       ' 用注册表存储执行标记
       Dim hasExecuted As Boolean
       hasExecuted = GetSetting(AppName:="AnalysisProject", Section:="Flags", Key:="StartupRan", Default:=False)
       
       If Not hasExecuted Then
           ' 执行原有更新逻辑
           ThisWorkbook.Activate
           Set myCurrentWorkbook = ThisWorkbook
           
           do some stuffs...
           
           Call my_copy
           
           ' 标记已执行
           SaveSetting AppName:="AnalysisProject", Section:="Flags", Key:="StartupRan", Setting:=True
       End If
   End Sub
   
   ' 关闭工作簿时清除标记
   Private Sub Workbook_BeforeClose(Cancel As Boolean)
       DeleteSetting AppName:="AnalysisProject", Section:="Flags", Key:="StartupRan"
   End Sub
   

3. 禁用事件避免冲突：关闭工作簿B前禁用Excel事件，防止交互冲突：

   Sub my_copy()
       Dim myWorkbook As Workbook
       Dim ws As Worksheet
       
       ' 临时禁用事件
       Application.EnableEvents = False
       
       Set myWorkbook = Workbooks.Open(myPath)
       Set ws = myWorkbook.Worksheets(mySheet)
       
       do some stuff
       
       myWorkbook.Save
       myWorkbook.Close SaveChanges:=False ' 已Save过，避免重复保存
       
       ' 释放对象+恢复事件
       Set ws = Nothing
       Set myWorkbook = Nothing
       Application.EnableEvents = True
   End Sub
   

4. 检查工作簿B的事件代码：确认工作簿B中没有Workbook_BeforeClose等事件内的异常代码，比如未处理的错误或循环逻辑。

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