Replacing VBA code strings by using VBA code?
 

Does anyone know a fast way to replace a VBA string IN a VBA module by
using VBA code? So I'm not talking about replacing strings in
worksheets...

Suppose that I have workbook A with the following sub in a codemodule:
-Sub Example()
MsgBox "Just an example.", vbOKOnly
End Sub-

So now I want to use VBA code in workbook B to change the string
"Example" in the codemulde of A to "ShowMessage", so that the new sub
in A will be:
-Sub ShowMessage()
MsgBox "Just an example.", vbOKOnly
End Sub-

Macro security settings aside, I know you need a reference to the MS
VBA Extensibilty, but my problem is the following. The object
"codemodule" has no method "replace".
So these are valid codelines:
-Workbooks("A.xls").VBProject.VBComponents(1).CodeM odule.Find
Workbooks("A.xls").VBProject.VBComponents(1).CodeM odule.ReplaceLine-
but:
-Workbooks("A.xls").VBProject.VBComponents(1).CodeM odule.Replace-
doesn't exist...

All suggestions are welcome. Thanks in advance.
shoba


--
shoba
------------------------------------------------------------------------
shoba's Profile: http://www.excelforum.com/member.php...o&userid=24676
View this thread: http://www.excelforum.com/showthread...hreadid=382617

Replacing VBA code strings by using VBA code?
 

Try something like the following:

Dim SL As Long, EL As Long, SC As Long, EC As Long
Dim S As String
Dim Found As Boolean
With ThisWorkbook.VBProject.VBComponents("Module1").Cod eModule
SL = 1
SC = 1
EL = 99999
EC = 999
Found = .Find("find this", SL, SC, EL, EC, True, False,
False)
If Found = True Then
S = .Lines(SL, 1)
S = Replace(S, "find this", "replace with")
.ReplaceLine SL, S
End If
End With


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




"shoba"
wrote in message
...

Does anyone know a fast way to replace a VBA string IN a VBA
module by
using VBA code? So I'm not talking about replacing strings in
worksheets...

Suppose that I have workbook A with the following sub in a
codemodule:
-Sub Example()
MsgBox "Just an example.", vbOKOnly
End Sub-

So now I want to use VBA code in workbook B to change the
string
"Example" in the codemulde of A to "ShowMessage", so that the
new sub
in A will be:
-Sub ShowMessage()
MsgBox "Just an example.", vbOKOnly
End Sub-

Macro security settings aside, I know you need a reference to
the MS
VBA Extensibilty, but my problem is the following. The object
"codemodule" has no method "replace".
So these are valid codelines:
-Workbooks("A.xls").VBProject.VBComponents(1).CodeM odule.Find
Workbooks("A.xls").VBProject.VBComponents(1).CodeM odule.ReplaceLine-
but:
-Workbooks("A.xls").VBProject.VBComponents(1).CodeM odule.Replace-
doesn't exist...

All suggestions are welcome. Thanks in advance.
shoba


--
shoba
------------------------------------------------------------------------
shoba's Profile:
http://www.excelforum.com/member.php...o&userid=24676
View this thread:
http://www.excelforum.com/showthread...hreadid=382617