Delete all VBA Modules - Except a Few

[dj44]

Hi folks,

:)

I am trying to delete all modules from my workbook except 2 or 3 that I need.

So I put this code together from research




Sub DeleteModulesExcept()

Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent

Set VBProj = ActiveWorkbook.VBProject

For Each VBComp In ActiveWorkbook.VBProject.VBComponents

If VBComp.VBProj.VBComponents("DontDelete1") or VBComponents("DontDelete2") Then
'Dont Delete
Else
VBProj.VBComponents.Remove VBComp
End If
Next
End Sub


I tried to follow the logic but, well I'm not sure how to make it work

thank you if you can help or advise

dj

[GTO]

"VBComp.VBProj.VBComponents"

You are going a bit backwards there. We've already drilled down to the component in the For Each. Warning: air-code, but something along the lines of...


For Each VBComp in VBProj.VBComponents
If Not (VBComp.Name = "NotMe" Or VBComp.Name = "MeEither") Then
VBProj.VBComponents.Remove VBComp
End If
Next


NOT TESTED, so please in a junk copy of your wb...

Mark

[dj44]

Hi Mark,

i initially tried the If NOT - but could not get it to work

I get an error on this line now
invalid call to procedure

VBProj.VBComponents.Remove VBComp

thanks
dj

[GTO]

I am signing out dj, but I would suggest attaching a workbook with the code exactly as you have it now.

[dj44]

Hi folks,
attached file

dj

[snb]

NB. Put this macro in the codemodule of 'thisWorkbook'


Sub M_snb()
ThisWorkbook.VBProject.VBComponents("dont_delete").Export "G:\OF\doont"

With Workbooks.Add
.VBProject.VBComponents.Import "G:\OF\doont"
End With
End Sub

or


Sub M_snb()
For Each it In ThisWorkbook.VBProject.VBComponents
If it.Type = 1 And it.Name <> "Dont_Delete" Then ThisWorkbook.VBProject.VBComponents.Remove it
Next
End Sub

[dj44]

Thank you SNB,

you are a PRO :thumb

I only spent the early morning with so many code lines up to my eyes :grinhalo:

Have a good day my friends

cheers
dj

[Paul_Hossler]

I had this in a module named 'Dont_Delete'

1. You need to check the module .Type also



Option Explicit
'http://www.cpearson.com/excel/vbe.aspx
Sub DeleteModulesExcept()

Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent

Set VBProj = ActiveWorkbook.VBProject

For Each VBComp In ActiveWorkbook.VBProject.VBComponents
Debug.Print "Checking " & VBComp.Name
If VBComp.Type = vbext_ct_StdModule Then
If VBComp.Name = "Dont_Delete" Or VBComp.Name = "Module2" Then
'Dont Delete
Else
Debug.Print "Removing " & VBComp.Name
VBProj.VBComponents.Remove VBComp
End If
End If
Next
End Sub

[dj44]

Thank you Paul,

you can never have too many versions of code

:grinhalo:

I can use this too
dj

[snb]

If you are interested see : http://www.snb-vba.eu/VBA_Excel_VBproject_en.html#L31