Generating a List of Macros

@AngelonTo list Personal.xlsb macros, either put the Tip's ListMacros procedure inside Personal.xlsb or change ThisWorkbook to Workbooks("Personal.xlsb"). For more on this subject, see Peter Atherton's comments below.

This is a handy macro and it works great. One question, how can this be used on the personal.xlsb file?

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 n    Dim VBComp As Object 'not VBComponentnthen you don't need to reference the Microsoft Visual Basic for Applications Extensibility library. nAlso, you should change ThisWorkbook to ActiveWorkbook as suggested by Peter.nSee http://www.cpearson.com/excel/vbe.aspx

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

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

Hi Allen,nI '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.nnOnly one workbook open at a time.nnthe code stops atnnStartLine = StartLine + .ProcCountLines(.ProcOfLine(StartLine, _nvbext_pk_Proc), vbext_pk_Proc)nnerror is "sub or function not defined"nnwhen 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.nnthis function is SO handy. what could be the problem. it works fine in another workbook

PeternnThank you very, very much for going the extra mile and making this a very, very useful macro. nnI 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.nnI'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.nn

This is a really useful macro - Kudos to Allen!!nnI 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.nnnOption ExplicitnnSub ListMacros()nDim VBComp As VBComponentnDim wsTarget As Worksheet, wks As WorksheetnDim wkb As WorkbooknDim StartLine As LongnDim iRow As IntegernnWith Applicationn .ScreenUpdating = Falsen .DisplayAlerts = FalsenEnd WithnSet wkb = ActiveWorkbooknWith wkbn For Each wks In ActiveWorkbook.Sheetsn If wks.Name = "ListMacros" Thenn wks.Deleten End Ifn Nextn Set wsTarget = Worksheets.Addn wsTarget.Name = "ListMacros"n wsTarget.Range("A1") = "Modules' Name"n wsTarget.Range("b1") = "Macros' Name"n wsTarget.Range("A1:B1").Font.Bold = Truen With wsTarget.Range("A1:b1").Borders(xlEdgeBottom)n .LineStyle = xlContinuousn End Withnn iRow = 2nn For Each VBComp In wkb.VBProject.VBComponentsn With VBComp.CodeModulen StartLine = .CountOfDeclarationLines + 1n Do Until StartLine >= .CountOfLinesn wsTarget.Cells(iRow, 1) = VBComp.CodeModule.Namen wsTarget.Cells(iRow, 2) = _n .ProcOfLine(StartLine, vbext_pk_Proc)n iRow = iRow + 1n StartLine = StartLine + _n .ProcCountLines(.ProcOfLine(StartLine, _n vbext_pk_Proc), vbext_pk_Proc)n Loopn End Withn Next VBCompnn wsTarget.Range("A1:b1").EntireColumn.AutoFitn With Applicationn .ScreenUpdating = Truen .DisplayAlerts = Truen End WithnnEnd WithnEnd Subnn

Walternnadd this line above the macro name (after the do loopnnwsTarget.Cells(iRow, 1) = VBComp.CodeModule.Namenand change the code name column to 2n

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.nnIs this left as an exercise for the student? nnOr, will there be a part two next week?nnOr, if it is easy enough, could someone please extend the macro so that we can also quickly located in its module?

@StefannnI 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". nnI'm running Office 365 and have LOTS of Macros in an Excel Workbook with 12 Worksheets.and at least 66 Macros.nI have 'discovered' the ability to list the Macros in VBA which can be "stepped into" or "edited". nThis is quite nice because I have nearly 100 printed pages of VBA. When trying to find a specific Macro:n------nWith VBA on the monitor...n Across the top line you will find "Tools" -- click on it.n In the drop-down list you will find "Macros" -- click on it. You will find a list of all Macros in the Workbook.n 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.)n------n[ I can't validate that ALL macros are listed; it would simply take more time to compare lists than it is worth. ]nnI wish there was a similar facility for listing 'controls' as there likely over 250 'buttons' within the Workbook.nn

i get the error, "User-defined type not defined" referring to the statement: Dim VBComp As VBComponentnni've done everything else!