Find and Replace code in Sheet modules

[Guest]

All,
I made a small mistake in one line of code of which there are a trillion
copies. Basically, I want to write code to find and replace lines in the
sheet modules.

ex. The sheet module has a line of code
Call JonMessedUp

I want to find all instances of that line in all the sheet modules and
change them to
Call JonDidNotMessUp

That's it.
Thanks in advance!!

 

[Bob Phillips]

One workbook or many. If the former, try Find/Replace (Ctrl-H)

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Around a trillion different workbooks. Too many to do find and replace.

Thanks for the response though

 

[Guest]

Perhaps my initial question was poorly phrased. Let me try stating this once
again.

Situation:
I have over 300 workbooks which each have several sheet modules with one
line of bad code. I need to be able to find ALL mathcing incorrect lines and
replace them with correct lines. I would like to do this programmatically.

e.g.
every sheet module has the line:
Call foo1
I need to replace that line in ALL the sheet modules with
Call foo2

Any help would be greatly appreciated.

 

[Bob Phillips]

Jon,

A few questions.

Are the procedures that contain the code to replace all the same? It would
be far easier to delete the procedures and install new versions, but this is
only possible if they are all the same

Do you just want the code to replace the code, or also to iterate through
the workbooks and then replace the code?

If the answer to the latter is to get the workbooks as well, are they all in
on folder, or in folders within a folder, or all over the place?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Mr. Phillips,

Are the procedures that contain the code to replace all the same?

Yes, they are all the same. Basically, what it boils down to is the fact
that in the Call statements within the Sheet modules, I mistyped the name of
the Sub to be called. I do not need to change any code within the Subs
themselves.

Do you just want the code to replace the code, or also to iterate through
the workbooks and then replace the code?

I know how to iterate through workbooks. So assume the workbook is open and
all I need to do is access each sheet module within the workbook, then
find/replace the necessary line.

Thanks,
Jon

 

[Guest]

Mr. Phillips,

Are the procedures that contain the code to replace all the same?

Yes they are all the same. What it boils down to is the fact that in each
sheet module I mistyped the name of the Sub to call. The Subs themselves are
all fine (and not located in the sheet module).

Do you just want the code to replace the code, or also to iterate through
the workbooks and then replace the code?

I know how to navigate through the workbooks. Assume a workbook is open and
all that needs to be done is to iterate through each sheet module and
find/replace the necessary line in each.

 

[Bob Phillips]

Mr Jon,

This first routine deletes a procedure called MyMacro from a module called
Module1, and the second adds a procedure called MyProc to that module.
Change the procedure name and module name to your values, and also amend the
code in the second routine to your code.


Const vbext_pk_Proc = 0

'----------------------------------------------------------------
Sub DeleteProcedure()
'----------------------------------------------------------------
Dim oCodeModule As Object
Dim iStart As Long
Dim cLines As Long

Set oCodeModule =
ActiveWorkbook.VBProject.VBComponents("Module1").CodeModule
With oCodeModule
On Error GoTo dp_err:
iStart = .ProcStartLine("myProc", vbext_pk_Proc)
cLines = .ProcCountLines("myProc", vbext_pk_Proc)
.DeleteLines iStart, cLines
On Error GoTo 0
Exit Sub
End With

dp_err:
If Err.Number = 35 Then
MsgBox "Procedure does not exist"
End If
End Sub


'----------------------------------------------------------------
Sub AddProcedure()
'----------------------------------------------------------------
Dim oCodeModule As Object
Dim cLines As Long

Set oCodeModule =
ActiveWorkbook.VBProject.VBComponents("Module1").CodeModule
With oCodeModule
cLines = .CountOfLines + 1
.InsertLines cLines, _
"" & vbCrLf & _
"Sub MyProc()" & vbCrLf & _
" Msgbox ""Here is the new procedure"" " & vbCrLf & _
" Call JonDidNotMessUp" & vbCrLf & _
"End Sub"
End With

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Exactly what I was looking for. Thanks a bunch! However, I have one more
question:

I need to be able to do this to every sheet module in the workbook and I
won't know the name of each sheet module (could be sheet1 or sheet24 etc...).
Is there a way to cycle through the Sheet modules without knowing how many
there are and what their names may be, and then apply the previous
subroutines?

 

[Bob Phillips]

Jon,

Here is an amended version.

One question, is it an event procedure you are deleting/adding as that is
different?

Const vbext_pk_Proc = 0

Sub Control()
Dim sh As Worksheet

For Each sh In ActiveWorkbook.Worksheets
DeleteProcedure sh.CodeName, "MyProc"
AddProcedure sh.CodeName, "MyProc"
Next sh
End Sub

'----------------------------------------------------------------
Sub DeleteProcedure(module As String, proc As String)
'----------------------------------------------------------------
Dim oCodeModule As Object
Dim iStart As Long
Dim cLines As Long

Set oCodeModule =
ActiveWorkbook.VBProject.VBComponents(module).CodeModule
With oCodeModule
On Error GoTo dp_err:
iStart = .ProcStartLine(proc, vbext_pk_Proc)
cLines = .ProcCountLines(proc, vbext_pk_Proc)
.DeleteLines iStart, cLines
On Error GoTo 0
Exit Sub
End With

dp_err:
If Err.Number = 35 Then
MsgBox "Procedure does not exist"
End If
End Sub


'----------------------------------------------------------------
Sub AddProcedure(module As String, proc As String)
'----------------------------------------------------------------
Dim oCodeModule As Object
Dim cLines As Long

Set oCodeModule =
ActiveWorkbook.VBProject.VBComponents(module).CodeModule
With oCodeModule
cLines = .CountOfLines + 1
.InsertLines cLines, _
"" & vbCrLf & _
"Sub " & proc & "()" & vbCrLf & _
" Msgbox ""Here is the new procedure"" " & vbCrLf & _
" Call JonDidNotMessUp" & vbCrLf & _
"End Sub"
End With

End Sub




--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Bob, almost everything has been working wonderfully except the AddProcedure
subroutine. It seems to crash Excel (and I mean CRASH) every time I Step
Into the "End If" line. Very wierd. Any ideas why this might be happening?
If I comment out the AddProcedure call, then everything runs smoothly.

 

[Guest]

Reboot seemed to fix the problem. Everyhting is working great. Thanks for
all your help, Bob!!