Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Remove all VBA modules from excel file?

Tags:

excel

vba

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.

like image 407
user1283776 Avatar asked Aug 29 '13 18:08

user1283776


People also ask

How do you remove all macros in Excel VBA?

There are a few different ways that you can remove macros from an Excel spreadsheet. One way is to simply delete the macro code from the sheet. Another way is to use the "Clear All" button on the "Developer" tab. Finally, you can also save the file as a Macro-Free Workbook.


2 Answers

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

like image 52
Kazimierz Jawor Avatar answered Sep 19 '22 11:09

Kazimierz Jawor


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
like image 30
user1283776 Avatar answered Sep 22 '22 11:09

user1283776