Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Deleting empty modules (2000 SR-1)

    <img src=/S/help.gif border=0 alt=help width=23 height=15>

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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Deleting empty modules (2000 SR-1)

    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 <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>.

    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.

  3. #3
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting empty modules (2000 SR-1)

    <img src=/S/kiss.gif border=0 alt=kiss width=34 height=15> <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •