如何在MS Office 2013中的VBA模块中自动查找/替换代码?

问题描述

我有大量的Excel模板，其中包含需要更新的VBA代码.代码模块对象的Find方法仅返回true/false，而不返回找到的字符串的位置.

I have a large number of Excel Templates that contain VBA code that need to be updated. The Find method of the code-module object only returns true/false, not the location of the found string.

有什么方法可以自动执行查找和替换过程?

Is there any way to automate the find-and-replace procedure?

推荐答案

将此代码添加到启用了宏的新工作簿中.设置FIND_WHAT和REPLACE_WITH常量，打开其他工作簿并运行代码.

Add this code to a new macro-enabled workbook. Set the FIND_WHAT and REPLACE_WITH constants, open the other workbooks and run the code.

原始代码来自查尔斯·皮尔森的网站

警告:仅完成了基本测试！

WARNING: Only basic testing has been done!

Option Explicit

Sub ReplaceTextInCodeModules()

' Must add a reference to "Microsoft Visual Basic For Applications Extensibility 5.3"
' Also must set "Trust access to the VBA project object model"
' See the url below for more info on these.
' Based on code found at:
' Source: www.cpearson.com/excel/vbe.aspx Copyright 2013, Charles H. Pearson

Dim theWorkbook As Workbook
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule
Dim numLines As Long ' end line
Dim lineNum As Long
Dim thisLine As String
Dim message As String
Dim numFound As Long

Const FIND_WHAT As String = "findthis"
Const REPLACE_WITH As String = "replaced"

    numFound = 0

    For Each theWorkbook In Application.Workbooks
        If theWorkbook.Name <> ThisWorkbook.Name Then
            If theWorkbook.HasVBProject Then
                Set VBProj = theWorkbook.VBProject
                For Each VBComp In VBProj.VBComponents
                    'Set VBComp = VBProj.VBComponents("Module1")
                    Set CodeMod = VBComp.CodeModule

                    With CodeMod
                        numLines = .CountOfLines
                        For lineNum = 1 To numLines
                            thisLine = .Lines(lineNum, 1)
                            If InStr(1, thisLine, FIND_WHAT, vbTextCompare) > 0 Then
                                message = message & theWorkbook.Name & " | " & VBComp.Name & " | Line #" & lineNum & vbNewLine
                                .ReplaceLine lineNum, Replace(thisLine, FIND_WHAT, REPLACE_WITH, , , vbTextCompare)
                                numFound = numFound + 1
                            End If
                        Next lineNum
                    End With
                Next VBComp
            End If
        End If
    Next theWorkbook

    Debug.Print "Found: " & numFound
    If message <> "" Then
        Debug.Print message
    End If

End Sub

这篇关于如何在MS Office 2013中的VBA模块中自动查找/替换代码?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！