如何在Excel中程序化检测或删除Worksheet_Deactivate代码

你提出的这两个方案都完全可行，而且都是解决这类VBA代码重复添加问题的常用思路，我来给你具体讲讲怎么实现：

要避免重复添加代码，核心是先判断目标工作表的代码模块里是否已经存在Worksheet_Deactivate过程。我们可以借助VBA的VBIDE对象模型来访问代码模块的内容，实现这一检测逻辑。

实现代码 #

首先写一个辅助函数，用来检查指定工作表是否已经存在目标过程：

Function IsDeactivateProcedureExists(ByVal ws As Worksheet) As Boolean
    Dim vbComp As VBIDE.VBComponent
    Dim proc As VBIDE.CodeModule
    
    Set vbComp = ThisWorkbook.VBProject.VBComponents(ws.CodeName)
    Set proc = vbComp.CodeModule
    
    ' 检查是否存在名为Worksheet_Deactivate的过程
    IsDeactivateProcedureExists = proc.ProcExists("Worksheet_Deactivate", vbext_pk_Proc)
End Function

然后在你添加代码的主宏里，先调用这个函数判断，存在就跳过添加：

Sub AddDeactivateCode()
    Dim ws As Worksheet
    
    For Each ws In ThisWorkbook.Worksheets
        ' 跳过你的重复条目列表工作表，避免误操作
        If ws.Name <> "重复条目列表" Then
            If Not IsDeactivateProcedureExists(ws) Then
                ' 这里添加你的Worksheet_Deactivate代码
                With ws.CodeModule
                    .InsertLines .CountOfLines + 1, "Private Sub Worksheet_Deactivate()"
                    .InsertLines .CountOfLines + 1, "    On Error Resume Next"
                    .InsertLines .CountOfLines + 1, "    Me.Shapes(""返回重复列表按钮"").Delete"
                    .InsertLines .CountOfLines + 1, "    On Error GoTo 0"
                    .InsertLines .CountOfLines + 1, "End Sub"
                End With
            End If
        End If
    Next ws
End Sub

这个方案更直接——不管目标过程是否存在，先尝试删除它（用错误处理忽略“过程不存在”的报错），再重新添加新代码。这样可以保证每次运行宏后，工作表里的Worksheet_Deactivate代码都是最新且唯一的。

实现代码 #

先写一个辅助函数用来删除目标过程：

Sub DeleteDeactivateProcedure(ByVal ws As Worksheet)
    Dim vbComp As VBIDE.VBComponent
    Dim proc As VBIDE.CodeModule
    Dim startLine As Long, endLine As Long
    
    On Error Resume Next ' 忽略过程不存在的错误
    Set vbComp = ThisWorkbook.VBProject.VBComponents(ws.CodeName)
    Set proc = vbComp.CodeModule
    
    ' 获取过程的起始和结束行
    startLine = proc.ProcStartLine("Worksheet_Deactivate", vbext_pk_Proc)
    endLine = proc.ProcCountLines("Worksheet_Deactivate", vbext_pk_Proc)
    
    ' 如果找到过程，就删除
    If startLine > 0 Then
        proc.DeleteLines startLine, endLine
    End If
    On Error GoTo 0
End Sub

然后在主宏里调用这个函数，先删再加：

Sub AddDeactivateCode()
    Dim ws As Worksheet
    
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "重复条目列表" Then
            ' 先删除已有的过程（如果存在）
            DeleteDeactivateProcedure ws
            
            ' 再添加新的Worksheet_Deactivate代码
            With ws.CodeModule
                .InsertLines .CountOfLines + 1, "Private Sub Worksheet_Deactivate()"
                .InsertLines .CountOfLines + 1, "    On Error Resume Next"
                .InsertLines .CountOfLines + 1, "    Me.Shapes(""返回重复列表按钮"").Delete"
                .InsertLines .CountOfLines + 1, "    On Error GoTo 0"
                .InsertLines .CountOfLines + 1, "End Sub"
            End With
        End If
    Next ws
End Sub

注意：这两个方案都需要你在Excel的信任中心里启用「对VBA项目对象模型的信任访问」，否则会报错。路径是：文件→选项→信任中心→信任中心设置→宏设置→勾选该选项。

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