Editing excel modules from VB 1

[johnbrewster]

I am formatting excel workbooks from a VB app and I need to perform a search and replace on one of the macro/modules in the worksheet. I cannot find the way to call the module/macro collection that lets me edit them.

 

[Loomah]

Hi
I've never done this so can't comment on it's usability and I don't know if it's accessible from VB but this site MAY be of some use to you!

http://www.cpearson.com/excel/vbe.htm

;-) If a man says something and there are no women there to hear him, is he still wrong?

 

[combo]

You need a reference to VBA Extensibility library.

Here an example how to add a line to a "Module1"
[tt]Dim cMod As VBComponent
Set cMod = ThisWorkbook.VBProject.VBComponents.Item("Module1&quot
With cMod.CodeModule
.InsertLines .CountOfLines + 1, "' new line"
End With[/tt]

You can use ProcStartLine, ProcCountLines properties of CodeModule object to find procedure, InsertLines, RepLaceLines, DeleteLine to edit code.
See VBA help for details.

combo