如何编写Excel个人宏工作簿宏，实现工作表与VBA模块批量查找替换？

核心实现方案 #

要操作VBE中的模块，需先引用Microsoft Visual Basic for Applications Extensibility 5.3库，之后可以遍历目标工作簿的标准模块（或指定模块），读取代码内容完成替换后再写回模块。

完整宏代码 #

将这段代码放到你的个人宏工作簿（PERSONAL.XLSB）的标准模块中：

Sub BatchFindReplace()
    Dim targetWB As Workbook
    Dim ws As Worksheet
    Dim vbaMod As VBComponent
    Dim modCode As String
    Dim findText As String
    Dim replaceText As String
    
    ' 定义查找和替换的文本（双引号需要用两个双引号转义）
    findText = """"""
    replaceText = "/"
    
    ' 锁定当前激活的工作簿作为处理目标
    Set targetWB = ActiveWorkbook
    
    ' --- 处理工作表单元格 ---
    For Each ws In targetWB.Worksheets
        ws.Cells.Replace What:=findText, Replacement:=replaceText, _
            LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
    Next ws
    
    ' --- 处理VBA标准模块 ---
    On Error Resume Next
    For Each vbaMod In targetWB.VBProject.VBComponents
        ' 只处理Module1、Module2这类标准模块
        If vbaMod.Type = vbext_ct_StdModule Then
            ' 读取模块所有代码
            modCode = vbaMod.CodeModule.Lines(1, vbaMod.CodeModule.CountOfLines)
            ' 执行文本替换
            modCode = Replace(modCode, findText, replaceText)
            ' 清空原代码并写入新内容
            vbaMod.CodeModule.DeleteLines 1, vbaMod.CodeModule.CountOfLines
            vbaMod.CodeModule.AddFromString modCode
        End If
    Next vbaMod
    On Error GoTo 0
    
    MsgBox "查找替换完成！", vbInformation
End Sub

操作步骤 #

• 1. 打开个人宏工作簿：如果之前没启用过，先随便录制一个宏并选择保存到「个人宏工作簿」，之后PERSONAL.XLSB会自动生成。
• 2. 启用VBA扩展库：按Alt+F11打开VBE，点击顶部菜单「工具」→「引用」，找到并勾选Microsoft Visual Basic for Applications Extensibility 5.3。
• 3. 配置信任权限：打开Excel的「文件」→「选项」→「信任中心」→「信任中心设置」→「宏设置」，勾选「信任对VBA项目对象模型的访问」。
• 4. 运行宏：打开需要处理的工作簿，按Alt+F8选择BatchFindReplace执行即可。

可选调整：只处理指定模块 #

如果不想遍历所有模块，只想处理Module1、Module2这类指定模块，把代码中处理模块的循环替换成以下内容：

' 定义要处理的模块名称数组
Dim modNames As Variant
modNames = Array("Module1", "Module2")
Dim modName As Variant

For Each modName In modNames
    Set vbaMod = targetWB.VBProject.VBComponents(modName)
    If Not vbaMod Is Nothing And vbaMod.Type = vbext_ct_StdModule Then
        modCode = vbaMod.CodeModule.Lines(1, vbaMod.CodeModule.CountOfLines)
        modCode = Replace(modCode, findText, replaceText)
        vbaMod.CodeModule.DeleteLines 1, vbaMod.CodeModule.CountOfLines
        vbaMod.CodeModule.AddFromString modCode
    End If
Next modName

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