Excel VBA中Change事件结束后为何返回SelectionChange子过程中途？

需求 #

用户选中D列单元格时，窗口缩放至120%且该单元格移至窗口左上角；离开该单元格后，焦点移至同行E列单元格，窗口缩放回100%且A1单元格回到窗口左上角，逻辑由Sheet1中的VBA代码实现。

异常现象 #

• 用户选中第4列单元格，触发Worksheet_SelectionChange事件，窗口缩放至120%并定位单元格至窗口左上角。
• 用户输入任意字符串，触发Worksheet_Change事件，代码选中同行右侧单元格，再次触发SelectionChange事件。
• SelectionChange事件通过Exit Sub测试返回至Change事件中断处。
• 返回Change事件后，调整缩放至100%，本该终止的Change事件却返回至SelectionChange事件的Exit Sub退出点继续执行。

额外调试发现：Change事件即将结束时ToCol变量值为5，但跳转至SelectionChange时ToCol变为4，代码并未主动修改该变量。

核心问题 #

为何Change事件子过程终止后，SelectionChange子过程会从中途位置重新执行？

Sheet1模块代码 #

Option Explicit
Public SystemBuzy As Boolean
Dim FromCell As Range, ToCell As Range
Dim BuzyHere As Boolean, LargeTarget As Boolean, FromIsEmpty As Boolean, ToIsEmpty As Boolean
Dim FromRow As Long, FromCol As Long, ToRow As Long, ToCol As Long

Private Sub Worksheet_Change(ByVal Target As Range) ' Value Change 
Call SetPrevious(Target)
Debug.Print "       Value Changed"

BuzyHere = BuzyHere Or SystemBuzy
LargeTarget = IsRangeLarge(FromCell)
    If BuzyHere Or LargeTarget Then
        Exit Sub
    End If

If ToRow > 2 And ToCol = 4 And Not ToIsEmpty Then
    BuzyHere = True
    ToCol = 5
    Sheet1.Cells(ToRow, ToCol).Select
    ActiveWindow.Zoom = 100
    ActiveWindow.ScrollRow = 1
    ActiveWindow.ScrollColumn = 1
End If

If ToRow > 2 And ToCol = 5 And MathVal(ToCell) > 0.01 Then
    BuzyHere = True
'Call DoSomething Not related
    End If

BuzyHere = False
End Sub
    '_______________________________________________________________________

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Call SetPrevious(Target)
Debug.Print "   Selection Changed"

BuzyHere = BuzyHere Or SystemBuzy
LargeTarget = IsRangeLarge(FromCell)
    If BuzyHere Or LargeTarget Then
        Exit Sub
    End If
If ToRow > 2 And ToCol = 4 Then
    ActiveWindow.Zoom = 120
    Application.Goto ActiveCell, Scroll:=True
Else
    ActiveWindow.Zoom = 100
    ActiveWindow.ScrollRow = 1
    ActiveWindow.ScrollColumn = 1
End If
    
BuzyHere = False
End Sub

Private Sub SetPrevious(TheTarget As Range)
    Set ToCell = TheTarget.Cells(1, 1)
        If FromCell Is Nothing Then Set FromCell = ToCell
    FromCol = FromCell.Column: FromRow = FromCell.Row
    ToRow = ToCell.Row: ToCol = ToCell.Column
    FromIsEmpty = ToIsEmpty
    ToIsEmpty = IsEmpty(ToCell)
    Set FromCell = ToCell
    
Debug.Print "FromRow:" & FromRow, "FromCol:" & FromCol, "ToRow:" & ToRow, "ToCol:" & ToCol;
    
End Sub

标准模块代码 #

Option Explicit
Option Base 1

Public Function TrimMil(Brut As Double) As Double
    TrimMil = Int(Brut * 100 + 0.5) / 100
End Function

Public Function MathVal(TheCell As Variant) As Double
    If IsNumeric(TheCell.Value) Then
        MathVal = TrimMil(Val(TheCell.Value))
    Else
        MathVal = 0
    End If
End Function

Public Function IsRangeLarge(TheRange As Range) As Boolean
    If TheRange Is Nothing Then
        IsRangeLarge = False
        Exit Function
    End If
    
    If TheRange.Rows.Count > 1 Or TheRange.Columns.Count > 1 Then
        IsRangeLarge = True
        Exit Function
    End If
    
IsRangeLarge = False
End Function

根本原因 #

1. 事件嵌套与模块级变量状态污染：
   • 在Worksheet_Change中执行Sheet1.Cells(ToRow, ToCol).Select时，会直接触发Worksheet_SelectionChange事件，此时Change事件并未执行完毕，处于暂停状态。
   • 模块级变量BuzyHere已经被Change事件设为True，所以SelectionChange会触发Exit Sub退出，但此时SelectionChange的执行上下文并未被完全清理。
   • 当Change事件继续执行到BuzyHere = False时，模块级变量被重置，VBA的事件处理机制会因为之前的嵌套残留，出现看似“从中途继续执行SelectionChange”的异常，本质是模块级变量状态混乱引发的逻辑错误。
2. ToCol变量值异常：
   • ToCol是模块级变量，每次调用SetPrevious都会重新赋值。Change事件结束后，任何隐性的内部操作（如Excel自身的界面刷新触发的隐性事件）都会调用SetPrevious，此时FromCell和ToCell的状态被覆盖，导致ToCol变回4。

修复方案 #

1. 禁用事件避免嵌套触发：
   在Worksheet_Change中执行Select操作前，先禁用Excel事件，操作完成后再恢复，防止SelectionChange被嵌套触发：

   If ToRow > 2 And ToCol = 4 And Not ToIsEmpty Then
       BuzyHere = True
       Application.EnableEvents = False ' 临时禁用事件
       ToCol = 5
       Sheet1.Cells(ToRow, ToCol).Select
       ActiveWindow.Zoom = 100
       ActiveWindow.ScrollRow = 1
       ActiveWindow.ScrollColumn = 1
       Application.EnableEvents = True ' 恢复事件触发
   End If
   

2. 减少模块级变量依赖：
   将BuzyHere、ToCol等变量改为过程级变量，或者在每个事件开头明确重置状态，避免状态残留。例如在Worksheet_SelectionChange开头添加BuzyHere = False（需根据逻辑调整）。
3. 优化事件职责划分：
   窗口缩放、滚动逻辑完全放在SelectionChange事件中处理，Change事件仅负责切换选中单元格，后续的界面调整由SelectionChange根据新选中的单元格自动完成，从根源减少事件嵌套。

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