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 image from book  list all procedures.xlsm , is available on the companion CD-ROM.

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



Excel 2007 Power Programming with VBA
Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)
ISBN: 0470044012
EAN: 2147483647
Year: 2007
Pages: 319

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net