Cleaning Up VBA , get rid of unused Subs - How I did it

[JeffGrant]

Hi All,

I would like to express my sincere thanks to all who support my endevours on this forum. The value of the knowledge found here is unparralleled.

In my model development I literally have hundreds of unused Subs that have accumulated over time.

To clean up any unused code, I used this code to listed out all of the Subs in the project.

Sub ListOfMacros()
    On Error Resume Next '< error = reference already set
    'set reference to Microsoft Visual Basic for Applications
    'Extensibility 5.3
    ThisWorkbook.VBProject.References.AddFromGuid _
        "{0002E157-0000-0000-C000-000000000046}", 5, 3
    'now get the list of macros
    Call GetTheList
End Sub

Private Sub GetTheList()
    Dim NN&, Count&, MyList(800), List$
    'Change the 800 to suit your own needs
    Dim Component As VBComponent
    For Each Component In ActiveWorkbook. _
        VBProject.VBComponents
        With Component.CodeModule
            Count = .CountOfDeclarationLines + 1
            Do Until Count >= .CountOfLines
                MyList(NN) = .ProcOfLine(Count, _
                    vbext_pk_Proc)
                Count = Count + .ProcCountLines _
                    (.ProcOfLine(Count, vbext_pk_Proc), _
                    vbext_pk_Proc)
                Debug.Print MyList(NN)
                List = List & vbCr & MyList(NN)
                If Count < .CountOfLines Then NN = NN + 1
            Loop
        End With
        NN = NN + 1
    Next
    MsgBox List, , "List of Macros"
End Sub

By creating this list and then comparing it to a second list of Subs created with the Find function in MZ Tools (all my subs use the word Call, so they are easy to find), and comparing the two lists, it was very easy to determine what was unused code.

Cudos and thanks to aflashman on List all macros and UDFs in Excel 2010 - Programming for putting this together and MZ-Tools - Productivity Tools for Visual Studio .NET (C#, VB.NET), Visual Basic and VBA for creating these tools.

I hope this information is of value to those like me who have never been formally trained in VBA.

Regards