Possible to do a search/replace in the IDE Code window with VBA?

[EagleOne]

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 Deitrick]

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).CodeModule
myCode = .Lines(1, .CountOfLines)
myCode = Replace(myCode, WhatToFind, ReplaceWith)
.DeleteLines 1, .CountOfLines
.InsertLines .CountOfLines + 1, myCode
End With

End Sub

 

[r]

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).CodeModule
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/index.php/Excel-VBA/UsedRange-eccezioni-e-alternative.html

 

[EagleOne]

Bernie,

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

EagleOne

 

[Bernie Deitrick]

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

 

[EagleOne]

Thank you very much to Europe from US

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).CodeModule
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/index.php/Excel-VBA/UsedRange-eccezioni-e-alternative.html

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

 

[EagleOne]

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

 

[Andy Smith]

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/d...html/ab0766e1-7037-45ed-aa23-706f58358c0e.asp