Find and replace within VBA modules

[theta]

Hi all...

I was wondering what code I use to programmatically do a find/replace all within the VBA modules.

I have several references that need changing within the VBA code.

I can build a For Next loop for each VBE environment, but need the code to do a find/replace all for all items in the Project.

Any help appreciated.

[GTO]

What is the "all" and what is the "reference"?

Quite frankly, I have stayed way too busy since January and do not get to spend as much time learning as I would be fond of. Could you provide a couple of sample (zipped) Before and After workbooks (.xls) so that we could see what exactly we are piling through?

Thank you so much,

Mark

[theta]

I have this to start with. Might adjust the 'String' arguments to be actual object, and change to a function.

Doesn't include the replace functionality that I desire....

[vba]
Public Sub SearchCodeModule(wkb As String, vbModule As String, vbFind As String)
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 = Workbooks(wkb).VBProject
Set VBComp = VBProj.VBComponents(vbModule)
Set CodeMod = VBComp.CodeModule

FindWhat = vbFind

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
[/vba]

[snb]

rather straightforward:

[VBA]
Sub tst()
For Each vbc In ThisWorkbook.VBProject.VBComponents
c01 = Replace(vbc.codemodule.Lines(1, vbc.codemodule.countoflines), "xxx", "yyy")
vbc.codemodule.deletelines
vbc.codemodule.addfromstring c01
Next
End Sub
[/VBA]

[theta]

Thanks snb.

Different approach to the one I was thinking of. You physically delete the line and replace it with an adjusted line.

What should I 'Dim' vbc as? I assume it means VB Components?

[Aflatoon]

I think I can guess what the answer to that will be...

[dantzu]

Yeap....

[snb]

The code doesn't replace line by line, but by whole codemodule.

[theta]

Any major issues with this? I am going to loop through two ranges "rngOld" and "rngNew" on Worksheets("index") and feed these to the "xxx" , "yyy" so that I can do bulk changes...

[vba]
For Each vbaProj In Application.VBE.VBProjects

Set Application.VBE.ActiveVBProject = vbaProj

For Each vbc In vbaProj.VBComponents
c01 = Replace(vbc.codemodule.Lines(1, vbc.codemodule.countoflines), "xxx", "yyy")
vbc.codemodule.deletelines
vbc.codemodule.addfromstring c01
Next vbc

Next vbaProj
[/vba]

[Bob Phillips]

What should I 'Dim' vbc as? I assume it means VB Components?

Same as your VBComp.

[Bob Phillips]

Any major issues with this? I am going to loop through two ranges "rngOld" and "rngNew" on Worksheets("index") and feed these to the "xxx" , "yyy" so that I can do bulk changes...

[vba]
For Each vbaProj In Application.VBE.VBProjects

Set Application.VBE.ActiveVBProject = vbaProj

For Each vbc In vbaProj.VBComponents
c01 = Replace(vbc.codemodule.Lines(1, vbc.codemodule.countoflines), "xxx", "yyy")
vbc.codemodule.deletelines
vbc.codemodule.addfromstring c01
Next vbc

Next vbaProj
[/vba]

Why do you loop through all projects if you are only interested in the activeproject, or vice versa?

[Bob Phillips]

Also be aware that snb rarely gives working code, there is invariably logic or syntax errors which you are left to sort out yourself. This code snippet is no exception.

[theta]

Sorry typo. [vba]ActiveWorkbook[/vba] should not be in there...

[snb]

[vba]Sub snb()
For Each vbc In ThisWorkbook.VBProject.VBComponents
With vbc.codemodule
If .countoflines > 0 Then
c01 = Replace(.Lines(1, .countoflines), "xxx", "yyy")
.deletelines 1, .countoflines
.addfromstring c01
End If
End If
Next
End Sub[/vba]

[theta]

Thanks SNB...I was working on the following (very close)

The only problem I am having is that it is trying to access the calling module. I need a way of saying for each vbaProj, then testing that vbaProj <> thisvbaProj ?

[vba]
Public Sub FindReplaceAll(sMatch As String)

For Each vbaProj In Application.VBE.VBProjects
If (UCase(vbaProj.Name) Like (sMatch)) Then
'Set Application.VBE.ActiveVBProject = vbaProj
'Check if Project is unlocked
If vbaProj.Protection <> 1 Then
'Set Application.VBE.ActiveVBProject = vbaProj
For Each vbc In vbaProj.VBComponents
If vbc.codemodule.countoflines <> 0 Then
c01 = Replace(vbc.codemodule.Lines(1, vbc.codemodule.countoflines), "xxx", "yyy")
vbc.codemodule.deletelines
vbc.codemodule.addfromstring c01
End If
Next vbc
End If
End If
Next vbaProj

End Sub
[/vba]

Also (just for my own knowledge) - is there a clear way to distinguish between vbc types in code i.e. workbook vs module vs userform.

[snb]

look at the vbcomponents(j).type property

macromodule: 1
class: 2
Userform:3
workbook: 100
worksheet: 100

To distinguish between worksheet and workbook use .properties.count:

worksheet.properties.count=66
workbook.properties.count<>66

[theta]

I am *almost* there. I just need a way to prevent the code from running on the current module. It tried to overwrite itself as causes a 450 error.

Where I have put "Hello" I would need application.vbe.thisvbproject but I cannot find a control to reference "this" vbproject (as you can with worksheets etc)

?

EDIT : This seems to do the trick, any other input welcome

[vba]
Public Sub FindReplaceAll(sMatch As String)

Set vbaActive = Application.VBE.ActiveVBProject

For Each vbaProj In Application.VBE.VBProjects
If (UCase(vbaProj.Name) Like UCase((sMatch))) And (vbaProj.Name <> vbaActive.Name) Then
If vbaProj.Protection <> 1 Then
For Each vbc In vbaProj.VBComponents
With vbc.CodeModule
If .countoflines > 0 Then
c01 = Replace(.Lines(1, .countoflines), "xxx", "yyy")
.deletelines 1, .countoflines
.addfromstring c01
End If
End With
Next vbc
End If
End If
Next vbaProj

Set vbaActive = Nothing

End Sub
[/vba]

[snb]

[VBA]

Public Sub FindReplaceAll(sMatch As String)
For Each vbaProj In Application.VBE.VBProjects
if vbaProj.filename<>thisworkfbook.fullname then
If vbaProj.Protection <> 1 Then
For Each vbc In vbaProj.VBComponents
With vbc.CodeModule
If .countoflines > 0 Then
c01 = Replace(.Lines(1, .countoflines), "xxx", "yyy")
.deletelines 1, .countoflines
.addfromstring c01
End If
End With
Next vbc
End If
End If
Next

End Sub

[/VBA]

[theta]

[VBA]

Public Sub FindReplaceAll(sMatch As String)
For Each vbaProj In Application.VBE.VBProjects
if vbaProj.filename<>thisworkfbook.fullname then
If vbaProj.Protection <> 1 Then
For Each vbc In vbaProj.VBComponents
With vbc.CodeModule
If .countoflines > 0 Then
c01 = Replace(.Lines(1, .countoflines), "xxx", "yyy")
.deletelines 1, .countoflines
.addfromstring c01
End If
End With
Next vbc
End If
End If
Next

End Sub

[/VBA]

Whenever I run this on my projects I keep getting an automation error :/ prevent it from running any further. And to make it worse, it happens in the first module. These projects contain on event code etc, would that be causing it - and how can I get round it?

Run-time error '-2147417848 (80010108)
Method 'AddFromString' of object 'CodeModule' failed

[snb]

From where do you call this macro.

Are you sure you have access to all addins ?

What workbook causes the problem ?

Why do you want to replace text in codemodules from the start ?