基于单元格值隐藏行的VBA宏随机故障排查求助

问题背景 #

我编写了一段VBA宏，用于根据FacilityChoice单元格的值（通过数据验证列表输入）隐藏/显示指定行，但宏会无规律失效，出现两类问题：

• 调试器高亮Sub定义或初始If Not语句，删除并重新输入该行末尾字符可临时修复；
• 宏直接失效，调试器无任何反馈，该问题尚未解决。

原代码 #

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Application.Intersect(Range("FacilityChoice"), Range(Target.Address)) Is Nothing Then

    'Gen Office Scenario
    
    If ActiveWorkbook.Sheets("SNA Tool").Range("FacilityChoice").Value = ActiveWorkbook.Sheets("References").Range("E2") Then
    
        ActiveSheet.Rows(17 & ":" & 23).EntireRow.Hidden = False
        ActiveSheet.Rows(25).EntireRow.Hidden = False
        ActiveSheet.Rows(29 & ":" & 30).EntireRow.Hidden = False
        ActiveSheet.Rows(37 & ":" & 39).EntireRow.Hidden = False
        ActiveSheet.Rows(43 & ":" & 45).EntireRow.Hidden = False
        
    'POP Scenario
    
    ElseIf ActiveWorkbook.Sheets("SNA Tool").Range("FacilityChoice").Value = ActiveWorkbook.Sheets("References").Range("E3") Then
    
        ActiveSheet.Rows(17 & ":" & 23).EntireRow.Hidden = True
        ActiveSheet.Rows(25).EntireRow.Hidden = True
        ActiveSheet.Rows(29 & ":" & 30).EntireRow.Hidden = True
        ActiveSheet.Rows(37 & ":" & 39).EntireRow.Hidden = True
        ActiveSheet.Rows(43 & ":" & 45).EntireRow.Hidden = True
        
    'Warehouse Scenario

    ElseIf ActiveWorkbook.Sheets("SNA Tool").Range("FacilityChoice").Value = ActiveWorkbook.Sheets("References").Range("E4") Then
        ActiveSheet.Rows(17 & ":" & 23).EntireRow.Hidden = True
        ActiveSheet.Rows(25).EntireRow.Hidden = True
        ActiveSheet.Rows(29 & ":" & 30).EntireRow.Hidden = True
        ActiveSheet.Rows(37 & ":" & 39).EntireRow.Hidden = True
        ActiveSheet.Rows(43 & ":" & 45).EntireRow.Hidden = True
        
    'Rapid Scenario

    ElseIf ActiveWorkbook.Sheets("SNA Tool").Range("FacilityChoice").Value = ActiveWorkbook.Sheets("References").Range("E5") Then
    
        ActiveSheet.Rows(17 & ":" & 23).EntireRow.Hidden = True
        ActiveSheet.Rows(25).EntireRow.Hidden = True
        ActiveSheet.Rows(29 & ":" & 30).EntireRow.Hidden = True
        ActiveSheet.Rows(37 & ":" & 39).EntireRow.Hidden = True
        ActiveSheet.Rows(43 & ":" & 45).EntireRow.Hidden = True
        
    End If

End If

End Sub

故障原因分析 #

1. 特殊字符干扰：调试器高亮特定行，大概率是代码混入了不可见的特殊字符（如全角空格、非ASCII换行符），导致VBA解析器报错，重新输入行尾字符相当于清除了这些异常字符。
2. Active对象依赖：代码大量使用ActiveSheet和ActiveWorkbook，当工作簿或工作表切换时，宏会指向错误对象，导致逻辑失效且无报错提示。
3. 事件递归触发：修改行隐藏状态会再次触发Worksheet_Change事件，导致宏陷入递归循环，可能出现无响应或悄无声息的失效。
4. 冗余代码风险：多个ElseIf分支逻辑重复，复制粘贴过程中易引入隐藏错误，也增加了维护成本。

修复后的代码 #

Private Sub Worksheet_Change(ByVal Target As Range)
    ' 禁用事件，防止修改行状态时递归触发Change事件
    Application.EnableEvents = False
    
    ' 提前定义工作表对象，避免依赖Active状态
    Dim snaSheet As Worksheet
    Dim refSheet As Worksheet
    Set snaSheet = ThisWorkbook.Sheets("SNA Tool")
    Set refSheet = ThisWorkbook.Sheets("References")
    
    ' 直接用Target与命名区域交叉判断，简化写法
    If Not Application.Intersect(Target, snaSheet.Range("FacilityChoice")) Is Nothing Then
        Dim facValue As Variant
        facValue = snaSheet.Range("FacilityChoice").Value
        
        ' 先统一设置所有目标行隐藏，再针对特殊场景取消隐藏
        With snaSheet
            .Rows("17:23").EntireRow.Hidden = True
            .Rows(25).EntireRow.Hidden = True
            .Rows("29:30").EntireRow.Hidden = True
            .Rows("37:39").EntireRow.Hidden = True
            .Rows("43:45").EntireRow.Hidden = True
            
            ' 仅处理需要显示的Gen Office场景
            If facValue = refSheet.Range("E2").Value Then
                .Rows("17:23").EntireRow.Hidden = False
                .Rows(25).EntireRow.Hidden = False
                .Rows("29:30").EntireRow.Hidden = False
                .Rows("37:39").EntireRow.Hidden = False
                .Rows("43:45").EntireRow.Hidden = False
            End If
        End With
    End If
    
    ' 恢复事件触发
    Application.EnableEvents = True
End Sub

关键修复点 #

• 移除Active依赖：用ThisWorkbook和预先定义的工作表对象替代ActiveWorkbook/ActiveSheet，确保操作始终指向正确的工作表。
• 简化逻辑结构：先统一设置所有行隐藏，再单独处理需要显示的场景，减少冗余代码，避免分支重复带来的错误。
• 禁用事件递归：添加Application.EnableEvents = False，防止修改行状态时再次触发Worksheet_Change事件，避免无响应。
• 优化判断写法：直接使用Target与命名区域交叉判断，避免Range(Target.Address)的冗余转换，减少解析错误概率。
• 清除特殊字符：重新编写代码时使用纯ASCII字符，避免不可见特殊字符导致的解析故障。

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