Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)

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.

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