Deleting Code in 'ThisWorkbook'

[Ken Loomis]

After creating a report, I use the following to delete all the code in the
main module before passing it along:

Sub DeleteAllCodeInModule()

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

Set VBCodeMod = ThisWorkbook.VBProject.VBComponents("Module3").CodeModule
With VBCodeMod
StartLine = 1
HowManyLines = .CountOfLines
.DeleteLines StartLine, HowManyLines
End With

End Sub





Now I realize I need to also delete all the code that executes when the
workbook is first opened. How can I modify the code above to delete the
lines of code in ThisWorkbook?

 

[Ron de Bruin]

Hi Ken

There is code on Chip's site to delete all code in the project also.
Look again on the site

 

[Ken Loomis]

I tried using this from Chip's site:

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

but I get an error 13 Type mismathc on this line:

Set VBComps = ActiveWorkbook.VBProject.VBComponents

and jiust figured it woul dbe easier to modify the code to just remove the
lines of code from ThisWorkbbok, but I do not know how to reference that in
this line:

Set VBCodeMod = ThisWorkbook.VBProject.VBComponents("NewModule").CodeModule

 

[Bob Phillips]

You have to set a reference to the Microsoft Visual Basic for Applications
Extensibility library in the VBE (Tools>References)

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Tom Ogilvy]

When I run that code without the reference, I get a user defined type not
defined error. I suspect Ken has commited a typo and declared

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

rather than

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

with the "s" on the second line as shown at Chip's site. When I use the
incorrect declaration, I can reproduce the type mismatch error on the line
cited.