Deleting empty modules (2000 SR-1)

Topic

New Reply

I just opened the Visual Basic Editor for an .xls file that I’m supposed to be doing some automation for…
The Project Explorer lists over 100 standard modules (Don’t ask me how they got there!), and there are only 4 or 5 with code in them… I really don’t want to leave them there, and I don’t want to delete them all one at a time…

Is there a way (by code) to loop through the Standard Modules for this workbook, check to see if there is any code in it, and if it’s empty, delete the module?
They are all named “Module1”, “Module45”, etc…

Reply | Quote

Replies

Here is a procedure that will do what you want, BUT… by default, Excel will not allow you to manipulate modules in code – this is a security setting. You can change this setting, but you may want to reset after running this code.

In Excel, select the Security tab of Tools | Options…
Click Macro Security…
Activate the Trusted Sources tab.
Check the box labeled something like ‘Trust access to Visual Basic project’.
Click OK twice.

Now, switch to the Visual Basic Editor.
Select Tools | References…
Locate ‘Microsoft Visual Basic for Applications Extensibility 5.3’ in the list and check the corresponding box.
Click OK. This reference makes the code to manipulate code available .

Insert a new module.
Paste the following code into the new module:

Sub RemoveEmpties()
Dim i As Integer
Dim vbcs As VBComponents
Dim vbc As VBComponent
Set vbcs = Application.VBE.ActiveVBProject.VBComponents
For i = vbcs.Count To 1 Step -1
Set vbc = vbcs.Item(i)
If vbc.Type = vbext_ct_StdModule Or _
vbc.Type = vbext_ct_ClassModule Then
If vbc.CodeModule.CountOfLines < 3 Then
vbcs.Remove vbc
End If
End If
Next i
Set vbc = Nothing
Set vbcs = Nothing
End Sub

Click somewhere in this procedure and press F5 to run it.

If you like, you can remove the module containg RemoveEmpties now – you don't need it any more.

Reply | Quote

Hans… You are THE BEST!!
That is a verrrryyyy cool new thing to know! Thanks!

Reply | Quote