求助：中心文件中宏文档存在大量重复，如何通过API或宏移除？

问题描述 #

我的中心文件中的宏文档出现了大量重复宏，但我不清楚原因。请问如何通过API或其他宏来移除这些重复项？

解决方案 #

方法1：批量清理重复模块 #

如果重复的是整个代码模块，可使用以下VBA宏遍历所有模块，对比代码内容并删除完全重复的模块：

Sub RemoveDuplicateModules()
    Dim vbProj As VBIDE.VBProject
    Dim vbComp As VBIDE.VBComponent
    Dim codeText As String
    Dim moduleDict As Object
    Dim key As Variant
    
    Set moduleDict = CreateObject("Scripting.Dictionary")
    Set vbProj = ThisDocument.VBProject ' Excel环境改为ThisWorkbook.VBProject
    
    For Each vbComp In vbProj.VBComponents
        If vbComp.Type = vbext_ct_StdModule Then
            codeText = vbComp.CodeModule.Lines(1, vbComp.CodeModule.CountOfLines)
            codeText = CleanCodeText(codeText)
            If Not moduleDict.Exists(codeText) Then
                moduleDict.Add codeText, vbComp.Name
            Else
                vbProj.VBComponents.Remove vbComp
            End If
        End If
    Next vbComp
    
    MsgBox "重复模块清理完成！"
End Sub

Function CleanCodeText(rawText As String) As String
    Dim lines() As String
    Dim cleanLine As String
    Dim i As Integer
    
    lines = Split(rawText, vbCrLf)
    CleanCodeText = ""
    
    For i = LBound(lines) To UBound(lines)
        cleanLine = Trim(lines(i))
        If cleanLine <> "" And Left(cleanLine, 1) <> "'" Then
            CleanCodeText = CleanCodeText & cleanLine & vbCrLf
        End If
    Next i
    If Len(CleanCodeText) > 2 Then CleanCodeText = Left(CleanCodeText, Len(CleanCodeText) - 2)
End Function

使用步骤：

1. 打开目标文档，按Alt+F11打开VBA编辑器
2. 插入新模块，粘贴上述代码
3. 运行RemoveDuplicateModules宏
4. 提前在Office设置中启用「信任对VBA项目对象模型的访问」（文件→选项→信任中心→信任中心设置→宏设置）

方法2：清理模块内重复的宏过程 #

如果单个模块内存在重复的Sub/Function过程，可使用以下宏清理：

Sub RemoveDuplicateProcedures()
    Dim vbMod As VBIDE.CodeModule
    Dim procName As String
    Dim procStartLine As Integer, procEndLine As Integer
    Dim procType As VBIDE.vbext_ProcKind
    Dim procDict As Object
    Dim codeText As String
    Dim i As Integer
    
    Set procDict = CreateObject("Scripting.Dictionary")
    ' 指定目标模块，或改为遍历所有模块
    Set vbMod = ThisDocument.VBProject.VBComponents("Module1").CodeModule
    
    i = 1
    Do While i <= vbMod.CountOfLines
        procName = vbMod.ProcOfLine(i, procType)
        procStartLine = vbMod.ProcStartLine(procName, procType)
        procEndLine = vbMod.ProcEndLine(procName, procType)
        
        codeText = vbMod.Lines(procStartLine, procEndLine - procStartLine + 1)
        codeText = CleanCodeText(codeText)
        
        If Not procDict.Exists(codeText) Then
            procDict.Add codeText, procName
            i = procEndLine + 1
        Else
            vbMod.DeleteLines procStartLine, procEndLine - procStartLine + 1
        End If
    Loop
    
    MsgBox "重复过程清理完成！"
End Sub

关键提示 #

• 操作前务必备份文件，防止误删有效代码
• 若使用Office API（如PowerShell、C#），核心逻辑一致：遍历文档VBA项目，对比代码内容后删除重复项
• 重复宏常见诱因：多次导入同一模块、文档合并带入重复代码、宏录制重复执行等

内容的提问来源于stack exchange，提问作者Đức Lê