How to mass replace contents inside the Excel VBA itself (not in cells contents)?

Question

I have more than 500 excel files in a folder, each one containing the same version of a VBA which has errors. The errors can be easily fixed by commenting out a few lines of the VBA. All I could find is how to mass replace data inside the sheets but not inside the macro itself..

Is there a way to mass replace the contents of the VBA inside all files from "sometext_inside_vba" to "'sometext_inside_vba"?

Thank you!

This might help https://stackoverflow.com/questions/30848609/how-to-automate-find-replace-code-in-vba-modules-in-ms-office-2013 — SJR, Feb 28 '18 at 09:46

score 0 · Answer 1 · edited May 08 '18 at 21:49

This would check for the words Option Private Module and if it does not find them, it would insert them everywhere (defined as a constant) - Automatically add `Option Private Module` to all modules in VBA) If you switch a few lines it would do what you need.

Sub AddOptionPrivate()

    Const UP_TO_LINE = 5
    Const PRIVATE_MODULE = "Option Private Module"

    Dim objXL               As Object

    Dim objPro              As Object
    Dim objComp             As Variant
    Dim strText             As String

    Set objXL = GetObject(, "Excel.Application")
    Set objPro = objXL.ActiveWorkbook.VBProject

    For Each objComp In objPro.VBComponents
        If objComp.Type = 1 Then
            strText = objComp.CodeModule.Lines(1, UP_TO_LINE)

            If InStr(1, strText, PRIVATE_MODULE) = 0 Then
                objComp.CodeModule.InsertLines 2, PRIVATE_MODULE
            End If

        End If
    Next objComp

End Sub

In general, this is a must read - http://www.cpearson.com/excel/vbe.aspx see the SearchCodeModule from there:

Sub SearchCodeModule()
    Dim VBProj As VBIDE.VBProject
    Dim VBComp As VBIDE.VBComponent
    Dim CodeMod As VBIDE.CodeModule
    Dim FindWhat As String
    Dim SL As Long ' start line
    Dim EL As Long ' end line
    Dim SC As Long ' start column
    Dim EC As Long ' end column
    Dim Found As Boolean

    Set VBProj = ActiveWorkbook.VBProject
    Set VBComp = VBProj.VBComponents("Module1")
    Set CodeMod = VBComp.CodeModule        
    FindWhat = "findthis"        
    With CodeMod
        SL = 1
        EL = .CountOfLines
        SC = 1
        EC = 255
        Found = .Find(target:=FindWhat, StartLine:=SL, StartColumn:=SC, _
            EndLine:=EL, EndColumn:=EC, _
            wholeword:=True, MatchCase:=False, patternsearch:=False)
        Do Until Found = False
            Debug.Print "Found at: Line: " & CStr(SL) & " Column: " & CStr(SC)
            EL = .CountOfLines
            SC = EC + 1
            EC = 255
            Found = .Find(target:=FindWhat, StartLine:=SL, StartColumn:=SC, _
                EndLine:=EL, EndColumn:=EC, _
                wholeword:=True, MatchCase:=False, patternsearch:=False)
        Loop
    End With
End Sub

How to mass replace contents inside the Excel VBA itself (not in cells contents)?

1 Answers1