vba - scan documents for macro and replace macro text?

Question

Hi, I'm presented with a puzzle. Where I work there are a huge amount of Word templates which all contains an autonew eventhandle which contains some errors. And this error lies within all templates. And I was wondering if there might be a way of scanning a directory for templates which contains this macro and change the macro code slightly?

Could this be possible?

Answer 1

Yes, you can do that. You can access the VBA project of any document using:

Application.VBE.ActiveVBProject.VBComponents

Your project must have a reference to "Microsoft Visual Basic for Applications Extensibility".

To run the code, you must enable the "Trust Access to Visual Basic Project" option in Word, using

Tools->Macro->Security (Trusted Publishers tab)

The VBComponents collection contains all the standard modules, class modules, forms and "document" modules that the project contains. If you Google it, you'll find plenty of help on how to access/modify them.

EDIT: OK, some more detail. This method will search all the VbComponents of a document looking for a method with the specified name, and perform a search/replace within the first one it finds.

Public Sub ReplaceInProject(ByVal oDocument As Document, ByVal strMethodName As String, ByVal strFindText As String, ByVal strReplaceWithText As String)

    ' For each module (of any type - could use oVbComponent.Type to restrict
    ' this to certain types of module)

    Dim oVbComponent As VBComponent
    For Each oVbComponent In oDocument.VBProject.VBComponents

        Dim oCodeModule As CodeModule
        Set oCodeModule = oVbComponent.CodeModule

        ' See if we can find the method in this module

        Dim ixStartLine As Long
        ixStartLine = FindMethodStartLine(oCodeModule, strMethodName)

        If ixStartLine > 0 Then

            ' Get all the text of the method

            Dim numLines As Long
            numLines = oCodeModule.ProcCountLines(strMethodName, vbext_pk_Proc)

            Dim strLines As String
            strLines = oCodeModule.Lines(ixStartLine, numLines)

            ' Do the find/replace

            strLines = Replace(strLines, strFindText, strReplaceWithText)

            ' Replace the method text.

            oCodeModule.DeleteLines ixStartLine, numLines

            oCodeModule.InsertLines ixStartLine, strLines

        End If

    Next oVbComponent

End Sub

Private Function FindMethodStartLine(ByVal oCodeModule As CodeModule, ByVal strMethodName As String) As Long

    FindMethodStartLine = 0

    ' ProcStartLine will raise an error if the method is not found;
    ' we'll just ignore the error and return -1

    On Error Resume Next
    FindMethodStartLine = oCodeModule.ProcStartLine(strMethodName, vbext_pk_Proc)

End Function

Note that this will only work with Sub and Function methods, not property Get/Set/Let because I'm using vbext_pk_Proc. It's a PITA that you need to be explicit about this. Frankly, the whole API for the CodeModule component seems almost designed to frustrate. For example, while the VbComponent object has a Find method (which you'd think was a convenient way to find the text you're looking for), it actually returns True or False(!). Useful, I don't think!

The designers of this API must have had a really bad hangover when they did this.