Delete VBA Module

[Douglas]

I need to write a piece of code which will open a workbook and delete one of it's VBA modules.

Any suggections?
Thanks

[Russell Hauf]

You need to set a reference to the VBIDE in Tools-References from the VB editor (the name is "Microsoft Visual Basic X.X Extensibility"). Then you can do something like this:

Sub DelModule()

    Dim VBP As Object
    
    Set VBP = ActiveWorkbook.VBProject
    
    VBP.VBComponents.Remove (VBP.VBComponents("Module2"))
    Set VBP = Nothing
End Sub

HTH,

Russell

[Douglas]

Great - thanks.

Do I use a silimar method is I just decide to delete a Sub routine within a module?

[NateO]

Nope use something like what Chip has created:

Sub DeleteProcedure()

Dim VBCodeMod As CodeModule
Dim StartLine As Long
Dim HowManyLines As Long

Set VBCodeMod = ThisWorkbook.VBProject.VBComponents("NewModule").CodeModule
With VBCodeMod
StartLine = .ProcStartLine("MyNewProcedure", vbext_pk_Proc)
HowManyLines = .ProcCountLines("MyNewProcedure", vbext_pk_Proc)
.DeleteLines StartLine, HowManyLines
End With

End Sub

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

Define the module and procedure name.

HTH. Cheers, Nate

[brettvba]

What about just a single line of code like in workbookopen event?

[brettvba]

all i could come up with was

Sub DeleteProcedureWB()

DisplayAlerts = False
Dim VBCodeMod As Object
Dim StartLine As Long

Set VBCodeMod = ThisWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
With VBCodeMod
StartLine = (3)
.Deletelines StartLine
End With
ActiveWorkbook.Save
End Sub

it works but can u specify text to delete?