如何修复VBA Worksheet_Change事件触发时的Error 9错误？

错误原因分析 #

• 循环范围a=2 To 29超出目标区域B2:B26的对应行，A27-A29可能为空或无匹配工作表，调用ThisWorkbook.Sheets(wsName)时找不到对应表，触发下标越界错误。
• 未对wsName做空值校验，也未判断工作表是否存在，一旦A列单元格为空或表名错误就会报错。
• 事件处理时未禁用事件触发，可能导致重复触发Change事件；ScreenUpdating设置逻辑颠倒，应先关闭再恢复。

修复后的代码 #

Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range
    Dim sh As Worksheet
    Dim wsName As String
    Dim a As Integer
    Dim targetWs As Worksheet
    
    Set sh = ThisWorkbook.Sheets("Dashboard")
    Set KeyCells = sh.Range("B2:B26") ' 限定为Dashboard工作表的目标区域，避免跨表错误
    
    If Not Application.Intersect(KeyCells, Target) Is Nothing Then
        Application.EnableEvents = False ' 禁用事件，防止循环触发
        Application.ScreenUpdating = False ' 关闭屏幕刷新，提升运行效率
        
        ' 循环范围匹配目标区域行（2到26）
        For a = 2 To 26
            wsName = Trim(sh.Range("A" & a).Value)
            ' 校验工作表名称非空且存在
            If wsName <> "" Then
                On Error Resume Next ' 临时捕获错误
                Set targetWs = ThisWorkbook.Sheets(wsName)
                On Error GoTo 0 ' 恢复默认错误捕获
                
                If Not targetWs Is Nothing Then ' 确认工作表存在
                    ' 简化可见性判断逻辑
                    targetWs.Visible = (sh.Range("B" & a).Value <> 0)
                    Set targetWs = Nothing ' 释放对象
                End If
            End If
        Next a
        
        Application.ScreenUpdating = True
        Application.EnableEvents = True ' 恢复事件触发
    End If
End Sub

关键修改说明 #

• 修正循环范围：从2 To 29改为2 To 26，与目标区域B2:B26完全对应，避免处理无关联行。
• 锁定目标区域归属：将KeyCells指定为sh.Range("B2:B26")，确保始终指向Dashboard工作表的目标区域，避免因当前激活表变化出错。
• 增加存在性校验：通过临时错误捕获机制判断工作表是否存在，彻底避免下标越界。
• 简化逻辑：用targetWs.Visible = (sh.Range("B" & a).Value <> 0)替代嵌套If，代码更简洁。
• 事件与刷新控制：处理前禁用事件和关闭刷新，完成后恢复，避免重复触发事件并提升运行流畅度。

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