Excel Macro to find/replace in Sheets & VBA Modules

[FuseMP]

I am looking to write a macro to have in my Personal Macro Workbook that will be able to be activated and run a series of Find/Replace's both on the worksheets of any Spreadsheet I have open and within the VBA of any macros inside that spreadsheet. The worksheets part of this is easy, but I cannot find a way to do it to the Visual Basic Editor, and trying to record the macro seems to not pick up anything done in VBA. I do not care if the code is clever enough to go through every VBA module or it needs to go "module1", "module2" and so on and run the find/replaces for each module seperately (because I guarantee that none of them are named otherwise); but so far, I have not been able to find anything that works and does an automatic find/replace in Excel VBA.

If it helps as an example, I am looking to change any reference in the VBA of "" to "/"

I have found something online about using VBINDE to change constants; but I haven't been able to modify it to change text as above.

Thanks

 

[jkpieterse]

Here's some code to find a string and have the line selected where it was found:

Option Explicit

Sub LookInVBA(str2Find As String)
    Dim oComp As VBIDE.VBComponent
    Dim oBk As Workbook
    Dim lCount As Long
    Set oBk = ActiveWorkbook
    For Each oComp In oBk.VBProject.VBComponents
        Select Case oComp.Type
            Case vbext_ct_ClassModule, vbext_ct_Document, vbext_ct_StdModule
                For lCount = 1 To oComp.CodeModule.CountOfLines
                    If (oComp.CodeModule.Lines(lCount, 1) Like "*" & str2Find & "*") Then
                        oComp.CodeModule.CodePane.Show
                        SelectVBA oComp.CodeModule, lCount, Len(oComp.CodeModule.Lines(lCount, 1))
                        MsgBox "Found ya"
                    End If
                Next
        End Select
    Next
TidyUp:
    Application.Visible = True
    Exit Sub
locerr:
    If Err.Number = 1004 Then
            MsgBox "For this feature to work you must allow access to the VBA project Object model", vbOKOnly + vbExclamation
            Resume TidyUp
        End If
   
    Resume TidyUp
End Sub

Function SelectVBA(oFoundObj As CodeModule, lLine As Long, lengthOfLine As Long)
    Application.VBE.MainWindow.Visible = True
    Application.Visible = False
    oFoundObj.CodePane.Show
    Application.VBE.ActiveCodePane.SetSelection lLine, 1, lLine, lengthOfLine + 1
End Function

 

[Micron]

If this is a one-time thing I imagine you could open each module and do a find/replace (ctl+h) - but you can't find that utility?

 

[FuseMP]

It is a one time thing, however its a one time thing across about 100 spreadsheets and needing to find/replace 2 or 3 things in each; so im trying to avoid manually doing it 300 times with a macro that i can run once per spreadsheet.

 

[jkpieterse]

My free Flexfind utility (though it does not "do" VBA) might help. It allows you to do a S&R in multiple workbooks open in Excel in one swoop. Free Flexfind Excel add-in

Then the mz-tools VBE add-in has an "all open projects" search and replace option. But mz-tools isn't free.

 

[FuseMP]

Thanks, I will check both out