Removing modules via VBA
 

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

Removing modules via VBA
 

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

"ExcelMonkey" wrote in message
...
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

Removing modules via VBA
 

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.

In article ,
ExcelMonkey wrote:

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

Removing modules via VBA
 

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

EM

"Peter T" wrote:

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

"ExcelMonkey" wrote in message
...
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