Removing modules via VBA

[ExcelMonkey]

I am trying to use the following code (taken from a previous response to a
post by Bob Phillips) in a macro in my personal folder. It is attempting to
delete all the VBA modules in a target. I have tried to amend to only delete
modules unless its named "Print Routine". It is failing on the firsst line
of the For Next loop with Run Time Error 1004. I note that I am not sure
what role the variable VBComp is playing in the routine.

Dim VBComp As Object
Dim vbMod As Object
Dim RetainedModule As String

RetainedModule = "PrintRoutine"

For Each vbMod In ActiveWorkbook.VBProject.vbcomponents
If vbMod.Type = 1 And vbMod.Name = RetainedModule Then 'a module
with specific name
ActiveWorkbook.VBProject.vbcomponents.Remove vbMod
End If
Next vbMod

EM

 

[Peter T]

It works fine for me. Have you enabled Trust access to visual basic project
in your security settings if you are using xl2002/3

I note that I am not sure
what role the variable VBComp is playing in the routine.

No role at all, but as you are looping VBComponents it would seem slightly
more logical to use VBComp instead of vbMod

Regards,
Peter T

 

[JE McGimpsey]

Hmmm...This works for me, except that using vbMod.Name = RetainedModule
means that the only module that IS deleted is the one you ostensibly
want to save (I'd think you'd want <> instead).

VBComp doesn't play any role in the code you posted.

 

[ExcelMonkey]

Yup that was it, did not have the trust enabled. Didn't even know the option
existed. Thanks.

EM