Deleting VBA code

[hshayh0rn]

I'm working with EXCEL 2003 and I'm trying to use the following code:

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

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


When it runs I get the following error:

Compile Error - User -defined type not defined

I have checked to be sure that under tools-macros-security and trusted
publishers that both the check boxes are checked which I've seen in other
posts could be an issue. So, does anyone know what the issue might be related
to the error?

 

[Tom Ogilvy]

You would need a reference in the workbook to the Microsoft visual basic
extensibility library where the type CodeModule is defined.

Otherwise, you should declare that as the generic Object.

 

[Jim Thomlinson]

You need to reference your project to the Extensibility library.

Tools -> Reference -> Microsoft Visual Basic for Applications Extensibility
?.?

 

[hshayh0rn]

Reference for me is greyed out so that is a porblem. Is there something else
that needs to be enabled in order for that option to be active? Also, if I
make that reference in the library does that reference stay with the workbook
so it will work for anyone who uses it? I don't want everyone who needs to
use this have to make this reference. Tom mentioned declaring a generic
object. How would this be done?

 

[Tom Ogilvy]

Dim VBCodeMod As Object
Dim StartLine As Long
Dim HowManyLines As Long

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

A reference stays with the workbook, but that doesn't mean it will work in
another version of excel or even another implementation. In this case, you
don't need it if you change your declaration.

 

[hshayh0rn]

I received the following error message from users trying to use the
speadsheet I built: "Programatic access to Visual Basic Project is not
trusted"

I'm pretty sure the reason for the error is that some of the users running
the program do not have the trust access to visual basic project checked. I
have read here that there is no way to enable or check that option
programtically but I need some way around this error. Maybe someone could
help me change my code so I don't need that box checked. Here is the code
I'm using right now:

Sub DeleteAllVBA()

Dim VBComp As Object
Dim VBComps As Object

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

 

[Dave Peterson]

This is a security setting that is under the control of the user.

Security settings wouldn't be too secure if any developer could change them.

Maybe you should ask the users to toggle this setting.