Using MS Office VBE IDE objects,how to automate Find/Replace code in VBA Modules

I have a large number of Excel Templates that contain VBA code that need to be updated. The Find method of the codemodule object only returns true/false, not the location of the found string. Is there any way to automate the find-and-replace procedure?
jhseymour113Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GrahamSkanRetiredCommented:
You will have to look at the text of the module. You can use the Lines() method to get some or all of the code.
Sub FindTextInProject()
    Dim vbp As VBProject
    Dim vbComp As VBComponent
    Dim lLineCount As Long
    Dim strCodeText As String
    Dim n As Integer
    Dim iPos As Integer
    
    Set vbp = VBE.VBProjects("Normal")
    For Each vbComp In vbp.VBComponents
        lLineCount = vbComp.CodeModule.CountOfLines
        strCodeText = vbComp.CodeModule.Lines(1, lLineCount)
        iPos = InStr(strCodeText, "FindText")
        If iPos > 0 Then
            Debug.Print "'FindText' found in character position " & iPos & " of Module " & vbComp.Name
        End If
    Next vbComp
End Sub

Open in new window


The VBA Replace() can be used to change the text.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jhseymour113Author Commented:
Thanks for the quick answer. Just seems amazing after all these years that MS doesn't provide a method for doing this. But you've definitely given me the solution.
GrahamSkanRetiredCommented:
I can't speak for Microsoft with any authority, but I would guess that they would say that
- there is enough functionality to do what is required
- there is a very small audience for VB and VBA extensibility
- VBA is part of COM, which we started to deprecate in 2002 with .net
- even without the extensibility, automated code changes to a single module could be done by exporting the module as text, editing it with an automated editor and re-importing the modified code
jhseymour113Author Commented:
I agree with everything you say. If I had one or two templates, I wouldn't need any of this. I need to make a series of small changes to as many as 90+ templates. I already pulled all the common code into a single add-on, but these templates are just stubbornly different enough to require some minimal code in each one that is somewhat unique. For years, they never needed to be altered, but the move from Office 2003 to 2013 and from Access to SQL Server has forced a small set of unavoidable changes. Thanks for your insights and help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.