CODEWITHSUNDEEP
excelvba
user1283776
user1283776

Reputation: 21764

Remove all VBA modules from excel file?

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.

Upvotes: 11

Views: 26221

Answers (2)

user1283776
user1283776

Reputation: 21764

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

Upvotes: 1

Kazimierz Jawor
Kazimierz Jawor

Reputation: 19067

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).

Upvotes: 14

Related Questions