PDA

View Full Version : Find and replace within VBA modules



theta
11-01-2012, 03:28 AM
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
11-01-2012, 03:49 AM
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
11-01-2012, 03:53 AM
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....


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

snb
11-01-2012, 04:00 AM
rather straightforward:


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

theta
11-01-2012, 04:33 AM
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
11-01-2012, 05:00 AM
I think I can guess what the answer to that will be... ;)

dantzu
11-01-2012, 05:40 AM
Yeap....

snb
11-01-2012, 05:56 AM
The code doesn't replace line by line, but by whole codemodule.

theta
11-01-2012, 07:57 AM
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...


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

Bob Phillips
11-01-2012, 08:00 AM
What should I 'Dim' vbc as? I assume it means VB Components?

Same as your VBComp.

Bob Phillips
11-01-2012, 08:06 AM
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...


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


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

Bob Phillips
11-01-2012, 08:18 AM
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
11-01-2012, 08:33 AM
Sorry typo. ActiveWorkbook should not be in there...

snb
11-01-2012, 09:12 AM
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

theta
11-01-2012, 09:23 AM
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 ?


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


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
11-01-2012, 10:18 AM
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
11-02-2012, 07:19 AM
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


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

snb
11-02-2012, 11:36 AM
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

theta
11-05-2012, 03:20 AM
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


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
11-05-2012, 03:54 AM
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 ?

theta
11-05-2012, 04:03 AM
I do not have access to all the modules, that why I check the locked state.

There is one main workbook and 20 other workbooks that are 'referenced' to that one. They all contain code with on_event triggers etc.

I do not need to replace all code from line 1, that is just the code I have been working with?

Any improved code welcome :D

snb
11-05-2012, 04:23 AM
There's also a limit to my clairvoyance....

theta
11-05-2012, 04:31 AM
Public Sub FindReplaceAll(sMatch As String)
For Each vbaProj In Application.VBE.VBProjects
If vbaProj.filename<>thisworkfbook.fullname Then 'Not this workbook
If vbaProj.Protection <> 1 Then 'Project is unlocked
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


Don't see a problem in replacing all text, this is the approach adopted since the first few posts?

Projects are <> This project and are <> protected. Don't see any other issues?

snb
11-05-2012, 04:48 AM
You overlooked a typo in line 3 ?

theta
11-05-2012, 05:14 AM
You overlooked a typo in line 3 ?

Yes sorry that was corrected, I just quoted the original post.

I will keep working on this but I am perplexed with this error...

theta
11-05-2012, 08:13 AM
You overlooked a typo in line 3 ?

The whole AddFromString and InsertLines approach was giving me endless grief and so I settled on a different approach. I did have one question and hope that you can answer it as you seem very knowledgeable...

When I run this sub, it works perfectly but will not find Range("A1") if it is within a Find() function e.g.


LastRow = Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row


Is this intentional? It is very useful as I was going to manually revert these, instead it just ignores them!?

Final working code below for reference :


Public Sub FindAndReplace(Optional match As String)

'Dim refernces for the VBIDE environment
Dim VBAEditor As VBIDE.VBE
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule

'Dim procedure variables
Dim SL As Long, EL As Long, SC As Long, EC As Long
Dim S As String
Dim Found As Boolean
Dim sFind As String
Dim sReplace As String
Dim sMatch As String

If Not match = "" Then
sMatch = UCase(match)
Else
sMatch = "*"
End If

Set VBAEditor = Application.VBE
Set VBActive = Application.VBE.ActiveVBProject

sFind = "Range(" & """" & "A1" & """" & ")" 'Range("A1")
sReplace = "Range(RangeLookup(" & """" & "RANGE001" & """" & ", False" & "))" 'RangeLookup("RANGE001",False)

For Each VBProj In VBAEditor.VBProjects
If UCase(VBProj.Name) Like UCase(sMatch) And (VBProj.Name <> VBActive.Name) Then
If VBProj.Protection <> 1 Then
For Each VBComp In VBProj.VBComponents

Do
Found = True
With VBComp.CodeModule
SL = 1 'Start line
SC = 1 'Start column
EL = 99999 'End line
EC = 999 'End column

Found = .Find(sFind, SL, SC, EL, EC, True, False, False) 'Must be case matches to the Replace statement
If Found = True Then
Debug.Print SL 'SL result from Find
S = .Lines(SL, 1)
S = Replace(S, sFind, sReplace, , , vbTextCompare) 'Default vbBinaryCompare will produce a loop error when using case insensitive Find
.ReplaceLine SL, S
End If
End With
Loop Until Found = False

Next VBComp
Set VBComp = Nothing
End If
End If
Next VBProj

Set VBProj = Nothing
Set VBActive = Nothing
Set VBEditor = Nothing

End Sub