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