2003/ 2007

Is it possible, using VBA, to do a search/replace in another VBA module's IDE Code window with VBA?

If so, What objects are involved? Is there a link explaining the process?

TIA EagleOne

The code below requires a reference to MS VBA Extensibility (whichever version you have). This
does a global replace, so be careful that you are replacing a unique string...

HTH,
Bernie
MS Excel MVP

Sub ReplaceCodeInModule()
Dim myCode As String
Dim WhatToFind As String
Dim ReplaceWith As String
Dim modName As String
Dim myBook As Workbook

WhatToFind = "Hello"
ReplaceWith = "Hello World"
modName = "Module1"

Set myBook = ActiveWorkbook

With myBook.VBProject.VBComponents.Item(modName).CodeMo dule
myCode = .Lines(1, .CountOfLines)
myCode = Replace(myCode, WhatToFind, ReplaceWith)
.DeleteLines 1, .CountOfLines
.InsertLines .CountOfLines + 1, myCode
End With

End Sub

wrote in message
...
2003/ 2007

Is it possible, using VBA, to do a search/replace in another VBA module's IDE Code window with
VBA?

If so, What objects are involved? Is there a link explaining the process?

TIA EagleOne

Check online help about find:
Function Find(Target As String, StartLine As Long, StartColumn As Long,
EndLine As Long, EndColumn As Long, [WholeWord As Boolean = Falso],
[MatchCase As Boolean = Falso], [PatternSearch As Boolean = Falso]) As Boolean
of VBIDE.CodeModule

try ....

Sub test()
Dim WB As Workbook
Set WB = Workbooks("cartel2") '<< change with your name workbook
ReplaceCodeInModule WB, "module1", "Long", "Double", True

End Sub


Sub ReplaceCodeInModule( _
WB As Excel.Workbook, _
sModuleNmae As String, _
sFind As String, _
sReplace As String, _
Optional bWholeWord = False, _
Optional dMatchCase = False, _
Optional bPatternSearch = False)

Dim i As Long
With WB.VBProject.VBComponents.Item(sModuleNmae).CodeMo dule
Do Until .Find(sFind, i, 1, -1, -1, bWholeWord, _
dMatchCase, bPatternSearch) = False
.ReplaceLine i, Replace(.Lines(i, 1), _
sFind, sReplace)
Loop
End With

End Sub

regards
r


Il mio ultimo lavoro ...
http://excelvba.altervista.org/blog/...ternative.html


" wrote:

2003/ 2007

Is it possible, using VBA, to do a search/replace in another VBA module's IDE Code window with VBA?

If so, What objects are involved? Is there a link explaining the process?

TIA EagleOne

Bernie,

You are an MVP hero! The comprehensiveness of your help is a solution and an applied teaching
moment.

EagleOne

"Bernie Deitrick" <deitbe @ consumer dot org wrote:

The code below requires a reference to MS VBA Extensibility (whichever version you have). This
does a global replace, so be careful that you are replacing a unique string...

HTH,
Bernie
MS Excel MVP

Sub ReplaceCodeInModule()
Dim myCode As String
Dim WhatToFind As String
Dim ReplaceWith As String
Dim modName As String
Dim myBook As Workbook

WhatToFind = "Hello"
ReplaceWith = "Hello World"
modName = "Module1"

Set myBook = ActiveWorkbook

With myBook.VBProject.VBComponents.Item(modName).CodeMo dule
myCode = .Lines(1, .CountOfLines)
myCode = Replace(myCode, WhatToFind, ReplaceWith)
.DeleteLines 1, .CountOfLines
.InsertLines .CountOfLines + 1, myCode
End With

End Sub

wrote in message
.. .
2003/ 2007

Is it possible, using VBA, to do a search/replace in another VBA module's IDE Code window with
VBA?

If so, What objects are involved? Is there a link explaining the process?

TIA EagleOne

You are an MVP hero! The comprehensiveness of your help is a solution and an applied teaching
moment.

Stop... you're embarrassing me. I'm glad that my post helped you, but it certainly is not
comprehensive, nor is it well documented or explained - I guessed (rightly, it seems) that someone
who wanted to do what you requested could figure it out from the code without a lot of
hand-holding...

Bernie

Thank you very much to Europe from US

r wrote:

Check online help about find:
Function Find(Target As String, StartLine As Long, StartColumn As Long,
EndLine As Long, EndColumn As Long, [WholeWord As Boolean = Falso],
[MatchCase As Boolean = Falso], [PatternSearch As Boolean = Falso]) As Boolean
of VBIDE.CodeModule

try ....

Sub test()
Dim WB As Workbook
Set WB = Workbooks("cartel2") '<< change with your name workbook
ReplaceCodeInModule WB, "module1", "Long", "Double", True

End Sub


Sub ReplaceCodeInModule( _
WB As Excel.Workbook, _
sModuleNmae As String, _
sFind As String, _
sReplace As String, _
Optional bWholeWord = False, _
Optional dMatchCase = False, _
Optional bPatternSearch = False)

Dim i As Long
With WB.VBProject.VBComponents.Item(sModuleNmae).CodeMo dule
Do Until .Find(sFind, i, 1, -1, -1, bWholeWord, _
dMatchCase, bPatternSearch) = False
.ReplaceLine i, Replace(.Lines(i, 1), _
sFind, sReplace)
Loop
End With

End Sub

regards
r


Il mio ultimo lavoro ...
http://excelvba.altervista.org/blog/...ternative.html


" wrote:

2003/ 2007

Is it possible, using VBA, to do a search/replace in another VBA module's IDE Code window with VBA?

If so, What objects are involved? Is there a link explaining the process?

TIA EagleOne

Some tease, some pontificate, some provide solutions. Life goes on.

"Bernie Deitrick" <deitbe @ consumer dot org wrote:

You are an MVP hero! The comprehensiveness of your help is a solution and an applied teaching
moment.

Stop... you're embarrassing me. I'm glad that my post helped you, but it certainly is not
comprehensive, nor is it well documented or explained - I guessed (rightly, it seems) that someone
who wanted to do what you requested could figure it out from the code without a lot of
hand-holding...

Bernie

Some questions for you, Bernie:

1. In ReplaceCodeInModule, after you delete the original lines N of code,
isn't it illegal to insert at N+1?

2. Are you familiar with the Find method's "patternsearch" parameter, i.e.,
the regular-expression syntax it uses? I saw that someone posted this link
for the syntax, but that page is entitled 'JScript / Regular Expression
Syntax (Scripting)".

http://msdn.microsoft.com/library/de...6f58358c0e.asp

--
Andy Smith
Senior Systems Analyst
Standard & Poor''s, NYC



"Bernie Deitrick" wrote:

The code below requires a reference to MS VBA Extensibility (whichever version you have). This
does a global replace, so be careful that you are replacing a unique string...

HTH,
Bernie
MS Excel MVP

Sub ReplaceCodeInModule()
Dim myCode As String
Dim WhatToFind As String
Dim ReplaceWith As String
Dim modName As String
Dim myBook As Workbook

WhatToFind = "Hello"
ReplaceWith = "Hello World"
modName = "Module1"

Set myBook = ActiveWorkbook

With myBook.VBProject.VBComponents.Item(modName).CodeMo dule
myCode = .Lines(1, .CountOfLines)
myCode = Replace(myCode, WhatToFind, ReplaceWith)
.DeleteLines 1, .CountOfLines
.InsertLines .CountOfLines + 1, myCode
End With

End Sub

wrote in message
...
2003/ 2007

Is it possible, using VBA, to do a search/replace in another VBA module's IDE Code window with
VBA?

If so, What objects are involved? Is there a link explaining the process?

TIA EagleOne