Excel forms: identify unused code

Question

I am updating an excel app written by someone else (of course :)

I found lots of unused Sub CommandButtonXX_Click() subs, and I am not always sure if the button still exists. Is there a way (program, VBE interface, external tool) to do some cleanup while avoiding to delete the still in use code ?

The list at the top of the properties box does seem to be reliable, since it is kind of context sensitive: if you're in a tab, it displays only items of that tab.

Answer 1

An interesting question!

1. I have significantly modified Pearson's code Listing All Procedures In A Module to find all CommandButtonXX_Click code on each worksheet (excluding other subs),
2. then tried to match each CommandButtonXX_Click code to an actual button on that sheet.
3. If there is no match the button is deleted, and a Msgbox at the end lists all deletions

Coding the VBA Editor can be problematic so pls save your work beforehand. I have avoided early binding with the Extensibility library that Pearson has used.

[4 Oct 2012: Updated to work on UserForms rather than Sheets]

       SConst vbext_ct_MSForm = 3
Sub ListProcedures()
    Dim VBProj
    Dim VBComp
    Dim CodeMod
    Dim LineNum As Long
    Dim NumLines As Long
    Dim ProcName As String
    Dim ObjButton
    Dim ProcKind
    Dim strBadButtons As String
    Set VBProj = ActiveWorkbook.VBProject
    For Each VBComp In VBProj.vbcomponents
        If VBComp.Type = vbext_ct_MSForm Then
            Set CodeMod = VBComp.CodeModule
            With CodeMod
                LineNum = .CountOfDeclarationLines + 1
                Do Until LineNum >= .CountOfLines
                    ProcName = .ProcOfLine(LineNum, 0)
                    If ProcName Like "CommandButton*_Click" Then
                        Set ObjButton = Nothing
                        On Error Resume Next
                        Set ObjButton = VBComp.Designer.Controls(Replace(ProcName, "_Click", vbNullString))
                        On Error GoTo 0
                        If ObjButton Is Nothing Then
                            strBadButtons = strBadButtons & CodeMod.Name & "-" & Replace(ProcName, "_Click", vbNullString) & vbNewLine
                            .DeleteLines .ProcStartLine(ProcName, 0), .ProcCountLines(ProcName, 0)
                        End If
                    End If
                    LineNum = LineNum + 1
                Loop
            End With
        End If
    Next
    If Len(strBadButtons) > 0 Then MsgBox "Bad Buttons deleted" & vbNewLine & strBadButtons
End Sub

Answer 2

There's a free add-in tool called MZ-Tools that can be used to identify unused procedures (it can do a lot more as well). Here is the link: http://www.mztools.com/v3/download.aspx

Answer 3

I'm developing Rubberduck, an open-source COM add-in for the VBE written in C# that has a code inspections feature that, as of version 1.3 (next release!), will include an inspection that does exactly that:

This inspection isn't specifically looking for unused click handlers as the accepted answer is doing (and if any CommandButtonX was renamed to anything meaningful, then the accepted answer will not find them - but that's not what the original post was about) - it's looking for procedures that are never called, assuming Public procedures and functions in a standard module (i.e. exposed to the host application as "macros" or "user-defined functions"), are used outside of the VBA code.

This version only finds controls on forms, not on worksheets - so handler procedures for ActiveX controls located on a worksheet would actually show up as false positives.