Remove all VBA modules from excel file?

Question

Is it possible to remove all VBA modules from an Excel file using VBA?

The names of the modules if they exist at all are unknowns before running this script.

Answer 1

Obviously, you can. The following code will do the job:

Sub compact_code()

On Error Resume Next
    Dim Element As Object
    For Each Element In ActiveWorkbook.VBProject.VBComponents
        ActiveWorkbook.VBProject.VBComponents.Remove Element
    Next

End Sub

This will remove all modules including ClassModules and UserForms but keep all object modules (sheets, workbook).

Answer 2

Here is a similar alternative that removes only the ClassModules:

On Error Resume Next
With wbk.VBProject
    For x = .VBComponents.Count To 1 Step -1
        If .VBComponents(x).Type = vbext_ct_StdModule Then
            .VBComponents.Remove .VBComponents(x)
        End If
    Next x
End With
On Error GoTo 0