VBA Worksheet_Change事件当前作用域重复声明编译错误如何解决

报错原因 #

你遇到的「Compile error: Duplicate declaration in current scope」报错是因为同一VBA过程的作用域内不允许重复声明同名常量，你在同一个Worksheet_Change事件里两次定义了名为SOMESHEETS的常量，违反了VBA语法规则。

修复后可运行代码 #

我们可以将两类工作表的匹配常量命名区分开，同时复用插入列的公共逻辑避免代码冗余：

Private Sub Worksheet_Change(ByVal Target As Range)
    ' 分别定义-19、-20后缀工作表的匹配规则
    Const SOMESHEETS_19 As String = "*C-Proposal-19*MemberInfo-19*Schedule J-19*NOL-19*NOL-P-19*NOL-PA-19*Schedule R-19*Schedule A-3-19*Schedule A-19*Schedule H-19*"
    Const SOMESHEETS_20 As String = "*MemberInfo-20*C-Proposal-20*Schedule J-20*NOL-20*Schedule R-20*NOL-P-20*SchA-3-20*Schedule H-20*NOL-PA-20*Schedule A-20*Schedule A-5-20*"
    
    Dim KeyCells As Range, ColNum As Long
    Dim ws As Worksheet
    Dim targetMatchRule As String
    
    ' 触发单元格B30对应-19后缀工作表
    Set KeyCells = Me.Range("B30")
    If Not Application.Intersect(KeyCells, Target) Is Nothing Then
        targetMatchRule = SOMESHEETS_19
    End If
    
    ' 触发单元格B36对应-20后缀工作表
    If Len(targetMatchRule) = 0 Then
        Set KeyCells = Me.Range("B36")
        If Not Application.Intersect(KeyCells, Target) Is Nothing Then
            targetMatchRule = SOMESHEETS_20
        End If
    End If
    
    ' 无匹配触发单元格直接退出
    If Len(targetMatchRule) = 0 Then Exit Sub
    
    ' 公共插入列逻辑
    If IsNumeric(KeyCells.Value) Then
        ColNum = KeyCells.Value
        If ColNum > 0 Then
            For Each ws In ThisWorkbook.Worksheets
                If ws.Visible = xlSheetVisible Then
                    If CBool(InStr(LCase(targetMatchRule), LCase("*" & ws.Name & "*"))) Then
                        InsertColumnsOnSheet argSheet:=ws, argColNum:=ColNum
                    End If
                End If
            Next ws
        End If
    End If
End Sub

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