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

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

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



I can use this too
dj

[snb]

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