Remove Modules ALL Workbboks

[Jaye7]

I have been playing around with scripts trying to delete modules from ALL workbooks but no matter what I try it only deletes them from the current active workbook, can someone please help.

I have read other forum threads on this site and Chip Pearsons but nothing seemes to work.

Before anyone asks, YES the module names are correct.


The first code I tried was.

 Dim VBProj As VBIDE.VBProject
        Dim VBComp As VBIDE.VBComponent
 
        Set VBComps = ActiveWorkbook.VBProject.VBComponents
 
        For Each VBComp In VBComps
            Select Case VBComp.Type
                Case vbext_ct_StdModule
                    modName = VBComp.Name
                'If modName = "X_Ranges_And_Settings" Then
                    'VBComps.Remove VBComp
                  ' End If
                If modName = "X_Ranges_And_Settings1" Then
                    VBComps.Remove VBComp
                   End If
                If modName = "X_Ranges_And_Settings2" Then
                    VBComps.Remove VBComp
                   End If
 
            End Select
        Next VBComp

the next one I tried is

For Each Wb In Workbooks
    If Wb.Name <> "List1" Then
       Dim VBProj As VBIDE.VBProject
        Dim VBComp As VBIDE.VBComponent
 
        Set VBComps = ActiveWorkbook.VBProject.VBComponents
 
        For Each VBComp In VBComps
            Select Case VBComp.Type
                Case vbext_ct_StdModule
                    modName = VBComp.Name
                'If modName = "X_Ranges_And_Settings" Then
                    'VBComps.Remove VBComp
                  ' End If
                If modName = "X_Ranges_And_Settings1" Then
                    VBComps.Remove VBComp
                   End If
                If modName = "X_Ranges_And_Settings2" Then
                    VBComps.Remove VBComp
                   End If
 
            End Select
        Next VBComp
 
 
    End If
Next Wb

I have also tried

Sub DeleteModule()
        Dim VBProj As VBIDE.VBProject
        Dim VBComp As VBIDE.VBComponent
 
        Set VBProj = ActiveWorkbook.VBProject
        Set VBComp = VBProj.VBComponents("X_Ranges_And_Settings1")
        VBProj.VBComponents.Remove VBComp
    End Sub

 

[Jaafar Tribak]

Look at the first line in your code.You are applying the code to the activeworkbook not to All workbooks.

Set VBComps = ActiveWorkbook.VBProject.VBComponents


Try this :

Option Explicit

Private Sub RemoveModule(ModuleName As String)
 
    Dim VBProj As VBIDE.VBProject
    Dim VBComp As VBIDE.VBComponent

    For Each VBProj In Application.VBE.VBProjects
    
        For Each VBComp In VBProj.VBComponents
        
            If VBComp.Name = ModuleName Then VBProj.VBComponents.Remove VBComp
        
        Next VBComp
    
    Next VBProj
 
End Sub
 
Sub Test()
 
    RemoveModule ModuleName:="X_Ranges_And_Settings"
    RemoveModule ModuleName:="X_Ranges_And_Settings1"
    RemoveModule ModuleName:="X_Ranges_And_Settings2"
    
End Sub

 

[Jaye7]

Thanks for your reply Jaafer but it bugs out on the following line.

The error message says user defined type not defined.

Dim VBProj As VBIDE.VBProject

 

[Jaafar Tribak]

You will need to add a reference to the VBA Exntensibility library or you can declare the valriables as Object like :

Option Explicit
 
Private Sub RemoveModule(ModuleName As String)
 
    Dim VBProj [COLOR=red]As Object[/COLOR]
    Dim VBComp [COLOR=red]As Object[/COLOR]
 
    For Each VBProj In Application.VBE.VBProjects
 
        For Each VBComp In VBProj.VBComponents
 
            If VBComp.Name = ModuleName Then VBProj.VBComponents.Remove VBComp
 
        Next VBComp
 
    Next VBProj
 
End Sub
 
Sub Test()
 
    RemoveModule ModuleName:="X_Ranges_And_Settings"
    RemoveModule ModuleName:="X_Ranges_And_Settings1"
    RemoveModule ModuleName:="X_Ranges_And_Settings2"
 
End Sub

 

[Jaye7]

Thanks for your help Jaafer, much appreciated.