Replacing code at runtime
 

Ajit,

Presuming you know the module name, you can use cod elike the macro below,
which will require a reference to MS VBA Extensibility. This also assumes
that the line you're looking for is unique.

HTH,
Bernie
MS Excel MVP

Sub AddLineAfterExistingCode()
Dim myCode As String
Dim OldCode As String
Dim NewCode As String

OldCode = "MsgBox ""Hello"""
NewCode = "MsgBox ""Hello There"""

With ActiveWorkbook.VBProject.VBComponents.Item("Module Name").CodeModule

myCode = .Lines(1, .CountOfLines)
myCode = Replace(myCode, OldCode, OldCode & Chr(10) & NewCode)

..DeleteLines 1, .CountOfLines
..InsertLines .CountOfLines + 1, myCode

End With

End Sub


"Ajit" wrote in message
...
This forum had been of great help at all the times..

I have posted this issue before too..but may be I could not explain
properly...trying to give another shot.

I have couple of templates and i need to add a line of code to all of them
(to the same procedure in a same module)...i am able to find the procedure
and add a line of code at the end. But what if i had to do find some code
and
add it in next line....I am not able to find the linenumber of the code
that
i do find for.

Suggestions will be greatly appreciated.

Thanks,


--
Ajit

Replacing code at runtime
 

Bernie,
Thanks a zillion for your suggestion...it worked perfect.

Just for knowledge sake....is there a way to find out the line number of the
OldCode. Actually i was following the logic of finding the line number of the
OldCode and adding the NewCode to the next line....

Anyway thanks for your reply, it was a great help.

"Bernie Deitrick" wrote:

Ajit,

Presuming you know the module name, you can use cod elike the macro below,
which will require a reference to MS VBA Extensibility. This also assumes
that the line you're looking for is unique.

HTH,
Bernie
MS Excel MVP

Sub AddLineAfterExistingCode()
Dim myCode As String
Dim OldCode As String
Dim NewCode As String

OldCode = "MsgBox ""Hello"""
NewCode = "MsgBox ""Hello There"""

With ActiveWorkbook.VBProject.VBComponents.Item("Module Name").CodeModule

myCode = .Lines(1, .CountOfLines)
myCode = Replace(myCode, OldCode, OldCode & Chr(10) & NewCode)

..DeleteLines 1, .CountOfLines
..InsertLines .CountOfLines + 1, myCode

End With

End Sub


"Ajit" wrote in message
...
This forum had been of great help at all the times..

I have posted this issue before too..but may be I could not explain
properly...trying to give another shot.

I have couple of templates and i need to add a line of code to all of them
(to the same procedure in a same module)...i am able to find the procedure
and add a line of code at the end. But what if i had to do find some code
and
add it in next line....I am not able to find the linenumber of the code
that
i do find for.

Suggestions will be greatly appreciated.

Thanks,


--
Ajit

Replacing code at runtime
 

You can step through line by line: the .Find method just returns a boolean,
so it's not much good, IMO.

Sub FindCodeInModule()
Dim myCode As String
Dim SearchCode As String
Dim i As Integer

SearchCode = "MsgBox ""Hello"""

With ActiveWorkbook.VBProject.VBComponents.Item("Defaul tModule").CodeModule
For i = 1 To .CountOfLines
If .Lines(i, 1) = SearchCode Then
MsgBox "Found it at line " & i
End If
Next i
End With

End Sub

HTH,
Bernie
MS Excel MVP

"Ajit" wrote in message
...
Bernie,
Thanks a zillion for your suggestion...it worked perfect.

Just for knowledge sake....is there a way to find out the line number of
the
OldCode. Actually i was following the logic of finding the line number of
the
OldCode and adding the NewCode to the next line....

Anyway thanks for your reply, it was a great help.

"Bernie Deitrick" wrote:

Ajit,

Presuming you know the module name, you can use cod elike the macro
below,
which will require a reference to MS VBA Extensibility. This also
assumes
that the line you're looking for is unique.

HTH,
Bernie
MS Excel MVP

Sub AddLineAfterExistingCode()
Dim myCode As String
Dim OldCode As String
Dim NewCode As String

OldCode = "MsgBox ""Hello"""
NewCode = "MsgBox ""Hello There"""

With ActiveWorkbook.VBProject.VBComponents.Item("Module Name").CodeModule

myCode = .Lines(1, .CountOfLines)
myCode = Replace(myCode, OldCode, OldCode & Chr(10) & NewCode)

..DeleteLines 1, .CountOfLines
..InsertLines .CountOfLines + 1, myCode

End With

End Sub


"Ajit" wrote in message
...
This forum had been of great help at all the times..

I have posted this issue before too..but may be I could not explain
properly...trying to give another shot.

I have couple of templates and i need to add a line of code to all of
them
(to the same procedure in a same module)...i am able to find the
procedure
and add a line of code at the end. But what if i had to do find some
code
and
add it in next line....I am not able to find the linenumber of the
code
that
i do find for.

Suggestions will be greatly appreciated.

Thanks,


--
Ajit

Replacing code at runtime
 

Thanks!

"Bernie Deitrick" wrote:

You can step through line by line: the .Find method just returns a boolean,
so it's not much good, IMO.

Sub FindCodeInModule()
Dim myCode As String
Dim SearchCode As String
Dim i As Integer

SearchCode = "MsgBox ""Hello"""

With ActiveWorkbook.VBProject.VBComponents.Item("Defaul tModule").CodeModule
For i = 1 To .CountOfLines
If .Lines(i, 1) = SearchCode Then
MsgBox "Found it at line " & i
End If
Next i
End With

End Sub

HTH,
Bernie
MS Excel MVP

"Ajit" wrote in message
...
Bernie,
Thanks a zillion for your suggestion...it worked perfect.

Just for knowledge sake....is there a way to find out the line number of
the
OldCode. Actually i was following the logic of finding the line number of
the
OldCode and adding the NewCode to the next line....

Anyway thanks for your reply, it was a great help.

"Bernie Deitrick" wrote:

Ajit,

Presuming you know the module name, you can use cod elike the macro
below,
which will require a reference to MS VBA Extensibility. This also
assumes
that the line you're looking for is unique.

HTH,
Bernie
MS Excel MVP

Sub AddLineAfterExistingCode()
Dim myCode As String
Dim OldCode As String
Dim NewCode As String

OldCode = "MsgBox ""Hello"""
NewCode = "MsgBox ""Hello There"""

With ActiveWorkbook.VBProject.VBComponents.Item("Module Name").CodeModule

myCode = .Lines(1, .CountOfLines)
myCode = Replace(myCode, OldCode, OldCode & Chr(10) & NewCode)

..DeleteLines 1, .CountOfLines
..InsertLines .CountOfLines + 1, myCode

End With

End Sub


"Ajit" wrote in message
...
This forum had been of great help at all the times..

I have posted this issue before too..but may be I could not explain
properly...trying to give another shot.

I have couple of templates and i need to add a line of code to all of
them
(to the same procedure in a same module)...i am able to find the
procedure
and add a line of code at the end. But what if i had to do find some
code
and
add it in next line....I am not able to find the linenumber of the
code
that
i do find for.

Suggestions will be greatly appreciated.

Thanks,


--
Ajit

Replacing code at runtime
 

Bernie,

You can step through line by line: the .Find method just
returns a boolean,
so it's not much good, IMO.

The Find method takes StartLine, EndLine, StartColumn, and
EndColumn as BYREF arguments, and will populate those variables
with the location at which the searched-for text was found.

Dim StartLine As Long
Dim EndLine As Long
Dim StartCol As Long
Dim EndCol As Long
Dim Found As Boolean

With ThisWorkbook.VBProject.VBComponents("Module1").Cod eModule
StartLine = 1
EndLine = .CountOfLines
StartCol = 1
EndCol = 999
Found = .Find("FindWhat", StartLine, StartCol, EndLine,
EndCol)
End With
If Found Then
Debug.Print "Found at line: " & StartLine & " column: " &
StartCol
Else
Debug.Print "Not found"
End If



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
You can step through line by line: the .Find method just
returns a boolean,
so it's not much good, IMO.

Sub FindCodeInModule()
Dim myCode As String
Dim SearchCode As String
Dim i As Integer

SearchCode = "MsgBox ""Hello"""

With
ActiveWorkbook.VBProject.VBComponents.Item("Defaul tModule").CodeModule
For i = 1 To .CountOfLines
If .Lines(i, 1) = SearchCode Then
MsgBox "Found it at line " & i
End If
Next i
End With

End Sub

HTH,
Bernie
MS Excel MVP

"Ajit" wrote in message
...
Bernie,
Thanks a zillion for your suggestion...it worked perfect.

Just for knowledge sake....is there a way to find out the line
number of
the
OldCode. Actually i was following the logic of finding the
line number of
the
OldCode and adding the NewCode to the next line....

Anyway thanks for your reply, it was a great help.

"Bernie Deitrick" wrote:

Ajit,

Presuming you know the module name, you can use cod elike
the macro
below,
which will require a reference to MS VBA Extensibility. This
also
assumes
that the line you're looking for is unique.

HTH,
Bernie
MS Excel MVP

Sub AddLineAfterExistingCode()
Dim myCode As String
Dim OldCode As String
Dim NewCode As String

OldCode = "MsgBox ""Hello"""
NewCode = "MsgBox ""Hello There"""

With
ActiveWorkbook.VBProject.VBComponents.Item("Module Name").CodeModule

myCode = .Lines(1, .CountOfLines)
myCode = Replace(myCode, OldCode, OldCode & Chr(10) &
NewCode)

..DeleteLines 1, .CountOfLines
..InsertLines .CountOfLines + 1, myCode

End With

End Sub


"Ajit" wrote in message
...
This forum had been of great help at all the times..

I have posted this issue before too..but may be I could
not explain
properly...trying to give another shot.

I have couple of templates and i need to add a line of
code to all of
them
(to the same procedure in a same module)...i am able to
find the
procedure
and add a line of code at the end. But what if i had to do
find some
code
and
add it in next line....I am not able to find the
linenumber of the
code
that
i do find for.

Suggestions will be greatly appreciated.

Thanks,


--
Ajit

Replacing code at runtime
 

Chip,

Now THAT's handy to know. How do you determine when a method uses BYREF or
BYVAL?

Thanks,
Bernie

"Chip Pearson" wrote in message
...
Bernie,

You can step through line by line: the .Find method just
returns a boolean,
so it's not much good, IMO.

The Find method takes StartLine, EndLine, StartColumn, and
EndColumn as BYREF arguments, and will populate those variables
with the location at which the searched-for text was found.

Replacing code at runtime
 

This is one of the few methods that operate this way. (Another is
GetSelection - help is a little clearer on that if you already know it) I
found a good explanation of it one time, but I can't remember where it was.

Anyway, this article provides some insights:

http://support.microsoft.com/default...18&Product=vba
ACC97: How to Programmatically Create, Search, Replace, and Modify Code

it has a comment (and see the last paragraph).

Because the Find method is not aware of where any subroutine starts or
ends, and because there may be multiple copies of the string you are
searching for in the module, you must take care in selecting the correct
string. Once the Find method locates the string, it returns the string's
line number within the module.

If you wanted to search for a string within a specific subroutine within a
module, you could:

A. Use the Find method to locate and save the line number for the first
instance of the name of a specific subroutine.

B. Use the Find method to locate and save the line number of the
first instance of the string "End Sub" starting at the line number
captured in step A by using Find's startline argument.

C. Use the Find method to locate and save the line number of the
string you are searching for within the desired subroutine by
specifying the startline from step and the endline from step
B by using Find's startline and endline arguments.

In the Find method, the target, startline, startcolumn, endline, and
endcolumn arguments are not optional and are updated to the matching string
values when Find locates the string. For more information, please see
Microsoft Access Help for additional Find method arguments.


--
Regards,
Tom Ogilvy


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Chip,

Now THAT's handy to know. How do you determine when a method uses BYREF or
BYVAL?

Thanks,
Bernie

"Chip Pearson" wrote in message
...
Bernie,

You can step through line by line: the .Find method just
returns a boolean,
so it's not much good, IMO.

The Find method takes StartLine, EndLine, StartColumn, and
EndColumn as BYREF arguments, and will populate those variables
with the location at which the searched-for text was found.