Find and Replace code in Sheet modules

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!!

--
J

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

--

HTH

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


"Jon" <[email protected]> wrote in message
news:[email protected]...
> 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!!
>
> --
> J

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

Thanks for the response though

"Bob Phillips" wrote:

> One workbook or many. If the former, try Find/Replace (Ctrl-H)
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Jon" <[email protected]> wrote in message
> news:[email protected]...
> > 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!!
> >
> > --
> > J
>
>
>

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.

"Jon" wrote:

> 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!!
>
> --
> J

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)


"Jon" <[email protected]> wrote in message
news:[email protected]...
> 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.
>
> "Jon" wrote:
>
> > 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!!
> >
> > --
> > J

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

"Bob Phillips" wrote:

> 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)
>
>
> "Jon" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
> >
> > "Jon" wrote:
> >
> > > 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!!
> > >
> > > --
> > > J
>
>
>

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" wrote:

> 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)
>
>
> "Jon" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
> >
> > "Jon" wrote:
> >
> > > 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!!
> > >
> > > --
> > > J
>
>
>

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)


"Jon" <[email protected]> wrote in message
news:[email protected]...
> 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" wrote:
>
> > 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)
> >
> >
> > "Jon" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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.
> > >
> > > "Jon" wrote:
> > >
> > > > 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!!
> > > >
> > > > --
> > > > J
> >
> >
> >

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" wrote:

> 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)
>
>
> "Jon" <[email protected]> wrote in message
> news:[email protected]...
> > 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" wrote:
> >
> > > 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)
> > >
> > >
> > > "Jon" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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.
> > > >
> > > > "Jon" wrote:
> > > >
> > > > > 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!!
> > > > >
> > > > > --
> > > > > J
> > >
> > >
> > >
>
>
>

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)


"Jon" <[email protected]> wrote in message
news:[email protected]...
> 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" wrote:
>
> > 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)
> >
> >
> > "Jon" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" wrote:
> > >
> > > > 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)
> > > >
> > > >
> > > > "Jon" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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.
> > > > >
> > > > > "Jon" wrote:
> > > > >
> > > > > > 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!!
> > > > > >
> > > > > > --
> > > > > > J
> > > >
> > > >
> > > >
> >
> >
> >

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.

"Bob Phillips" wrote:

> 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)
>
>
> "Jon" <[email protected]> wrote in message
> news:[email protected]...
> > 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" wrote:
> >
> > > 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)
> > >
> > >
> > > "Jon" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" wrote:
> > > >
> > > > > 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)
> > > > >
> > > > >
> > > > > "Jon" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > 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.
> > > > > >
> > > > > > "Jon" wrote:
> > > > > >
> > > > > > > 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!!
> > > > > > >
> > > > > > > --
> > > > > > > J
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

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

"Bob Phillips" wrote:

> 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)
>
>
> "Jon" <[email protected]> wrote in message
> news:[email protected]...
> > 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" wrote:
> >
> > > 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)
> > >
> > >
> > > "Jon" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" wrote:
> > > >
> > > > > 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)
> > > > >
> > > > >
> > > > > "Jon" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > 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.
> > > > > >
> > > > > > "Jon" wrote:
> > > > > >
> > > > > > > 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!!
> > > > > > >
> > > > > > > --
> > > > > > > J
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>