Generating a List of Macros

Re. the Tip's ListMacros procedure, if you substitute procKind for each instance of vbext_pk_Proc as recommended in my previous comment and change the declaration of VBComp as follows
    Dim VBComp As Object 'not VBComponent
then you don't need to reference the Microsoft Visual Basic for Applications Extensibility library.
Also, you should change ThisWorkbook to ActiveWorkbook as suggested by Peter.
See http://www.cpearson.com/excel/vbe.aspx

@Kerry and Peter
To accommodate VBA procedures that are neither Sub nor Function, add the following statement to the Tip's ListMacros:
Dim procKind as Long
Then substitute procKind for each instance of vbext_pk_Proc (there are 3 instances).

My Excel Toolbox includes the following dynamic array function:
=ListProcs([SkipHeader])
This function returns the following 6 columns for each VBA procedure in the workbook:
Component (name), Type (module or user form), Procedure (name), Type (Sub or Function), Scope (Public or Private), Declaration. An example Declaration follows:
Public Function ListProcs(Optional SkipHeader As Boolean = False) As Variant()
In older versions of Excel you can use ListProcs with the SpillArray function like this:
=SpillArray(ListProcs([SkipHeader])
See https://sites.google.com/view/MyExcelToolbox/

Hi Allen,
I 've used this code in 2 workbooks, but it only works on 1. The excel settings are obviously identical.including the references in tools specific to the active workbook.

Only one workbook open at a time.

the code stops at

StartLine = StartLine + .ProcCountLines(.ProcOfLine(StartLine, _
vbext_pk_Proc), vbext_pk_Proc)

error is "sub or function not defined"

when I check the worksheet that contains the list, it as stopped at code in a form. I don't know if that is relevant or not.

this function is SO handy. what could be the problem. it works fine in another workbook

Peter

Thank you very, very much for going the extra mile and making this a very, very useful macro.

I was just about to take your previous suggestion of extending the macro when you are latest post appeared. I am sure you saved me many hours of time. I knew zilch about VBComponents, but am a little bit more knowledgeable about them now.

I'm sure there will be others like me who spend a lot of time trying to find a macro and can't remember its name or where it is. A table of contents for your macro workbook is a very useful feature that has been missing for years. Thanks to Allen for introducing this, and to you, for your very useful extension.

This is a really useful macro - Kudos to Allen!!

I copied it into my personal workbook and found it needed a bit of revision to work on the active workbook. The following is the revised code if you want to place it in the PERSONAL.XLSB workbook.


Option Explicit

Sub ListMacros()
Dim VBComp As VBComponent
Dim wsTarget As Worksheet, wks As Worksheet
Dim wkb As Workbook
Dim StartLine As Long
Dim iRow As Integer

With Application
.ScreenUpdating = False
.DisplayAlerts = False
End With
Set wkb = ActiveWorkbook
With wkb
For Each wks In ActiveWorkbook.Sheets
If wks.Name = "ListMacros" Then
wks.Delete
End If
Next
Set wsTarget = Worksheets.Add
wsTarget.Name = "ListMacros"
wsTarget.Range("A1") = "Modules' Name"
wsTarget.Range("b1") = "Macros' Name"
wsTarget.Range("A1:B1").Font.Bold = True
With wsTarget.Range("A1:b1").Borders(xlEdgeBottom)
.LineStyle = xlContinuous
End With

iRow = 2

For Each VBComp In wkb.VBProject.VBComponents
With VBComp.CodeModule
StartLine = .CountOfDeclarationLines + 1
Do Until StartLine >= .CountOfLines
wsTarget.Cells(iRow, 1) = VBComp.CodeModule.Name
wsTarget.Cells(iRow, 2) = _
.ProcOfLine(StartLine, vbext_pk_Proc)
iRow = iRow + 1
StartLine = StartLine + _
.ProcCountLines(.ProcOfLine(StartLine, _
vbext_pk_Proc), vbext_pk_Proc)
Loop
End With
Next VBComp

wsTarget.Range("A1:b1").EntireColumn.AutoFit
With Application
.ScreenUpdating = True
.DisplayAlerts = True
End With

End With
End Sub

Walter

add this line above the macro name (after the do loop

wsTarget.Cells(iRow, 1) = VBComp.CodeModule.Name
and change the code name column to 2

Good macro as far as it goes. It would be very much more useful to me if the macro also placed the module name/number of the extracted macro name in column B.

Is this left as an exercise for the student?

Or, will there be a part two next week?

Or, if it is easy enough, could someone please extend the macro so that we can also quickly located in its module?

@Stefan

I had the same problem. I fixed it by making sure the Microsoft Visual Basic for Applications Extensibility check box is selected in the VBAProject where I placed the macro code.

In the January 20th Excel Tips was a "Generating a List of Macros".

I'm running Office 365 and have LOTS of Macros in an Excel Workbook with 12 Worksheets.and at least 66 Macros.
I have 'discovered' the ability to list the Macros in VBA which can be "stepped into" or "edited".
This is quite nice because I have nearly 100 printed pages of VBA. When trying to find a specific Macro:
------
With VBA on the monitor...
Across the top line you will find "Tools" -- click on it.
In the drop-down list you will find "Macros" -- click on it. You will find a list of all Macros in the Workbook.
Within the listing you can 'Run", 'Step Into', or 'Edit' the macro. (Using this is quite useful since you don't have to search out the macro.)
------
[ I can't validate that ALL macros are listed; it would simply take more time to compare lists than it is worth. ]

I wish there was a similar facility for listing 'controls' as there likely over 250 'buttons' within the Workbook.

i get the error, "User-defined type not defined" referring to the statement: Dim VBComp As VBComponent

i've done everything else!