Deleting code from a macro (by a macro)

[Brettjg]

Hi there

I want to delete some code from a PRIVATE SUB in the sheet code after it has
been used. I copied the following code from Chip Pearson's web site but it
comes up with an error (it doesn't seem to like VBIDE very much). I have
checked the trust VB code checkbox in security options.

Sub DeleteAllVBA()

Dim VBComp As VBIDE.VBComponent
Dim VBComps As VBIDE.VBComponents

Set VBComps = ActiveWorkbook.VBProject.VBComponents

For Each VBComp In VBComps
Select Case VBComp.Type
Case vbext_ct_StdModule, vbext_ct_MSForm, _
vbext_ct_ClassModule
VBComps.Remove VBComp
Case Else
With VBComp.CodeModule
.DeleteLines 1, .CountOfLines
End With
End Select
Next VBComp

End Sub
My preference would be just to delete the code rather than the whole VB
Project, but it doesn't really matter. My understanding is that you can't
delete code from a Private sub anyway


Sub DeleteAllCodeInModule()
Dim VBCodeMod As CodeModule
Dim StartLine As Long
Dim HowManyLines As Long

Set VBCodeMod =
ThisWorkbook.VBProject.VBComponents("Worksheet_Change").CodeModule
With VBCodeMod
StartLine = 1
HowManyLines = .CountOfLines
.DeleteLines StartLine, HowManyLines
End With
'Private Sub Worksheet_Change
End Sub

This doesn't work either.


Can someone help please?

 

[Dave Peterson]

I think you missed this paragraph from Chip's page (near the top):

Before using these procedures, you'll need to set a reference in VBA to the VBA
Extensibility library. In the VBA editor, go to the Tools menu, choose the
References item, and put a check next to "Microsoft Visual Basic For
Applications Extensibility" library. This enables VBA to find the definitions
of these objects. If you are using Excel97, this library will appear in the
References list without a version number: "Microsoft Visual Basic For
Applications Extensibility". If you are using Excel 2000 or later, it will
appear with a version number: "Microsoft Visual Basic For Applications
Extensibility 5.3". It is very important that you reference the proper
library. If you reference the wrong library, you will receive "Type Mismatch"
errors. If you don't reference the extensibility library at all, you will
receive "User Defined Type Not Defined Error" messages.

 

[Brettjg]

Hi Dave, I think you might be right!. Thankyou for that.
Regards, Brett