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.
An interesting question!
- I have significantly modified Pearson’s code Listing All Procedures In A Module to find all
CommandButtonXX_Clickcode on each worksheet (excluding other subs),
- then tried to match each
CommandButtonXX_Clickcode to an actual button on that sheet.
- If there is no match the button is deleted, and a
Msgboxat 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
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
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.