Listing All VBA Procedures in a Workbook

The ListProcedures macro in this section creates a list (in a message box) of all VBA procedures in the active workbook.

 Sub ListProcedures()     Dim VBP As VBIDE.VBProject     Dim VBC As VBComponent     Dim CM As CodeModule     Dim StartLine As Long     Dim Msg As String     Dim ProcName As String '   Use the active workbook     Set VBP = ActiveWorkbook.VBProject '   Loop through the VB components     For Each VBC In VBP.VBComponents         Set CM = VBC.CodeModule         Msg = Msg & vbNewLine         StartLine = CM.CountOfDeclarationLines + 1         Do Until StartLine >= CM.CountOfLines             Msg = Msg & VBC.Name & ": " & _               CM.ProcOfLine(StartLine, vbext_pk_Proc) & vbNewLine             StartLine = StartLine + CM.ProcCountLines _              (CM.ProcOfLine(StartLine, vbext_pk_Proc), _               vbext_pk_Proc)         Loop     Next VBC     MsgBox Msg End Sub 

Figure 28-4 shows the result for a workbook that has nine procedures.

CD-ROM  

This example, named  list all procedures.xlsm , is available on the companion CD-ROM.

Figure 28-4: The message box lists all procedures in the active workbook.