excel macro to find and replace specific string in VBA modules

I am looking for help in a VBA code that when i run, it triggers search with input box that asks what string to be searched for in all open workbooks modules , user puts lets say  (Public Const CompanyName As String = "Zigwang" ) then next input box asks if search item found in the modules what it should be replace with. then user puts (Public Const CompanyName As String = "Yamishamo") then macro searches for all modules of all open workbooks and when this line  (Public Const CompanyName As String = "Zigwang" )  found it replaces it with (Public Const CompanyName As String = "Yamishamo") and on any modules that replaces are done, then it also saves the changes made.

thanks for your professional help.
LVL 6
FloraAsked:
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.

byundtMechanical EngineerCommented:
The "go to" source for sample code to programmatically edit VBA is posted by Microsoft Excel MVP Chip Pearson at http://www.cpearson.com/excel/VBE.aspx   The routines shown below are based on his work.

Here is some code that will find a specific string (input by user) with another string (also input by user). It will do so in every code module (worksheet code panes, ThisWorkbook and regular code modules) in every open workbook--except the one containing this code.
Sub ReplaceTextInCode()
'Brad Yundt wrote this sub
'Finds all instances of a user specified string in the VBA code and replaces it with another user specified string
Dim VBProjSource As VBIDE.VBProject, VBProjDest As VBIDE.VBProject
Dim VBComp As VBIDE.vbcomponent
Dim wbSource As Workbook, wbDest As Workbook
Dim ws As Worksheet
Dim n As Long, numModules As Long
Dim FindWhat As String, ReplaceWith As String

Set wbSource = ThisWorkbook
FindWhat = InputBox("What text in VBA code do you want to find?")
If FindWhat = "" Then Exit Sub

ReplaceWith = InputBox("What is its replacement text?", Default:=FindWhat)

Application.ScreenUpdating = False
For Each wbDest In Application.Workbooks
    If wbSource.name <> wbDest.name Then
        Set VBProjDest = wbDest.VBProject
        
        For Each VBComp In VBProjDest.VBComponents
            SearchCodeModule VBComp, FindWhat, ReplaceWith
        Next
        
        For Each ws In wbDest.Worksheets
            Set VBComp = VBProjDest.VBComponents(ws.CodeName)
            SearchCodeModule VBComp, FindWhat, ReplaceWith
        Next
        
        Set VBComp = VBProjDest.VBComponents("ThisWorkbook")
        SearchCodeModule VBComp, FindWhat, ReplaceWith

        'wbDest.Save        'Commented out because programmatically editing VBA code can be dangerous if not done correctly!
    End If
Next
End Sub

Sub SearchCodeModule(VBComp As VBIDE.vbcomponent, FindWhat As String, ReplaceWith As String)
'Searching For Text In A Module
'Code modified by Brad Yundt from Chip Pearson original. Modifications include passing parameters and replacing text
'The CodeModule object has a Find method that you can use to search for text within the code module. _
    The Find method accepts ByRef Long parameters. Upon input, these parameters specify the range of lines and column _
    to search. On output, these values will point to the found text. To find the second and subsequent occurence of the text, _
    you need to set the parameters to refer to the text following the found line and column. The Find method returns True or False _
    indicating whether the text was found. The code below will search all of the code in Module1 and print a Debug message _
    for each found occurrence. Note the values set with the SL, SC, EL, and EC variables. The code loops until the Found variable is False.

    Dim CodeMod As VBIDE.CodeModule
    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
    Dim sLine As String
    
    Set CodeMod = VBComp.CodeModule
    
    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)
            sLine = .Lines(SL, 1)
            sLine = Replace(sLine, FindWhat, ReplaceWith)
            .DeleteLines SL, 1
            .InsertLines SL, sLine
            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

Open in new window

***Important***

1. You must set a reference in this workbook for Microsoft Visual Basic Extensibility 5.3 in the VBA Editor. It may already be checked. To find it, open the Tools...References box in the VBA Editor and look for the reference at the top of the list. If not there, scroll down to find the reference. Either way, make sure its box is checked.
2. You must also check the box for Allow Access to the VBA project object model in the Macro Security menu item. To find it, use the Developer ribbon and choose Macro Security...Macro Settings menu item and tab.

Note
I commented out a statement that saves the workbook after updating its code. Programmatically editing VBA code can cause big problems if you don't do it 100% correctly.
ReplaceTextInVBA_Q28732379.xlsm
FloraAuthor Commented:
@byundt

you are truly a genius. the code worked amazingly.

it did replace the text with new text, but it did not save those workbooks from which the VBA was changed.
aikimarkCommented:
Since your example string contains a quote character, you should test whether or not you need to double-up those quote characters.
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

FloraAuthor Commented:
thank you aikimark.

i tired without doubling up qoute chars and still worked.
byundtMechanical EngineerCommented:
but it did not save those workbooks from which the VBA was changed.
Statement 24 in my suggested code included that feature, but was commented out. If you remove the single quote from the beginning of that statement, it will save the workbooks after the VBA editing is complete.

As discussed in my previous Comment, I did not feel comfortable saving the files after their code was edited programmatically in case the user made a mistake and changed more than he wanted to. You should test the code before saving the files.

As an alternative, how about a macro that saves all open workbooks except the one containing the VBA editing code, then optionally closes them? In the code below, I commented out the statement that closes the workbooks, but you can remove the single quote and enable it.
Sub SaveAllFiles()
Dim wb As Workbook
For Each wb In Application.Workbooks
    If wb.Name <> ThisWorkbook.Name Then
        wb.Save
        'wb.Close SaveChanges:=False    'Close the workbook after having saved it
    End If
Next
End Sub

Open in new window

ReplaceTextInVBA_Q28732379.xlsm

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
FloraAuthor Commented:
thanks a million byundt
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 Excel

From novice to tech pro — start learning today.