Remove all VBA modules from excel file?

Question

Can I remove all VBA modules from an Excel file using VBA?

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

Answer 1

Obviously you can. The following code will complete the task:

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 delete all modules, including ClassModules and UserForms, but save all object modules (sheets, workbook).

Answer 2

Here is a similar alternative that only removes 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