VBA TIPS
Remove empty modules from VBE project

I got a mail asks how to delete empty modules. Operation CodeModule is not so difficult. The following code loops through a VBAProject then counts the code lines. If Code lines count is less than the DeclarationLines, it means there is no available procedures in the module.

Regards to the Code Module Tips, Chip Pearson (Pearson Software Consulting, LLC) already has great tip site so please have a look at the following link.

http://www.cpearson.com/excel/vbe.htm


Option Explicit



Public Sub RemoveEmptyModules()
'Works only for Standard modules and Class modules
    Const vbext_pp_none As Long = 1
    Const vbext_ct_StdModule As Long = 1
    Const vbext_ct_ClassModule As Long = 2

    Dim objVbc As Object
    Dim lngStLine As Long
    Dim lngCntOfLines As Long
    Dim lngCntRemove As Long

    For Each objVbc In ActiveWorkbook.VBProject.VBComponents
        Select Case objVbc.Type
        Case vbext_ct_StdModule, vbext_ct_ClassModule
            lngStLine = objVbc.CodeModule.CountOfDeclarationLines + 1
            lngCntOfLines = objVbc.CodeModule.CountOfLines
            If lngCntOfLines < lngStLine Then
                'It means there is no availabale procedures in the module
                'So you can remove this module with the following line
                ActiveWorkbook.VBProject.VBComponents.Remove objVbc
                lngCntRemove = lngCntRemove + 1    'Counter
            End If
        End Select
    Next
    If lngCntRemove = 0 Then
        MsgBox "No empty modules are found."
    Else
        MsgBox lngCntRemove & " module(s) are removed."
    End If
End Sub

| HOME |
Copyright © cellmasters.net - colo's junk room All Right Reserved
ABOUT
WORKS
THE CELL MASTERS
CONTACT
LINKS
Tips and Information about Microsoft Excel|Masaru Kaji aka Colo