Excel中Workbook_SheetChange事件下标越界与触发死循环怎么解决

问题根因确认 #

1. 下标越界：工作簿级事件会响应所有工作表的修改操作，当触发事件的是Index表本身、工作表序号计算结果超出表格行索引范围、修改的行号小于5导致行索引为负时，都会触发该报错
2. 死循环：代码修改Index表的单元格内容时，会再次触发Workbook_SheetChange事件，无限递归最终导致程序报错

修复后代码 #

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    On Error GoTo ErrHandler
    ' 过滤不需要处理的工作表：跳过Index表本身，避免修改Index表时触发逻辑
    If Sh.Name = "Index" Then Exit Sub
    
    ' 校验表格行索引合法性，避免ListRows下标越界
    Dim targetListRow As Long
    targetListRow = Sh.Index - 2
    If targetListRow < 1 Or targetListRow > Sheets("Index").ListObjects("TabelaIndex").ListRows.Count Then Exit Sub
    
    ' 校验单元格行索引合法性，避免Range下标越界
    Dim targetRangeRow As Long
    targetRangeRow = Target.Row - 4
    If targetRangeRow < 1 Then Exit Sub
    
    ' 临时关闭事件触发，禁止修改单元格时递归触发当前事件
    Application.EnableEvents = False
    
    ' 执行赋值操作
    Sheets("Index").ListObjects("TabelaIndex").ListRows(targetListRow).Range(targetRangeRow).Value = Target.Value

ExitHandler:
    ' 强制恢复事件触发，避免后续Excel事件全部失效
    Application.EnableEvents = True
    Exit Sub
ErrHandler:
    MsgBox "运行错误：" & Err.Description, vbCritical
    Resume ExitHandler
End Sub

注意事项 #

• 如果只需要监控指定名称的工作表，可以把第一行的过滤规则调整为判断Sh.Name是否在指定名称列表中
• 如果需要支持批量修改多单元格的场景，可增加循环遍历Target的所有单元格分别处理

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