List Macros and their containing module

[DavidJB]

I have a lot of macros contained in several modules. Not sure how that happened but it now takes a while for me to find any individual macro. I would like to generate a list of all my macros and the modules they are stored in. The list should include Private Subs. I found some solutions which list the macros but they appear to exclude private subs and none show which module the macro is stored in.

 

[Micron]

Surprised I had this - don't remember writing it.

Sub LoopThroughModules()
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule
Dim LineNum As Long

Set VBProj = ThisWorkbook.VBProject

For Each VBComp In VBProj.VBComponents
   Debug.Print VBComp.Name
   Set CodeMod = VBComp.CodeModule
   Debug.Print CodeMod.Lines(1, 3)
Next
            
End Sub

I believe the line print arguments there mean lines 1 and 3 and that includes blank lines.

 

[Micron]

A further look and I realize the grouping isn't quite right. One result I get is
Module1
Option Explicit

Sub TestCallFromQAT()
Module2
Option Explicit

It should look like

Module1
Option Explicit
Sub TestCallFromQAT()

Module2
Option Explicit
#If VBA7 Then

It also needs tweaking to get not just lines 1 to 3 but whatever it is you need. I don't know if code can find each procedure and return its name or if you'd have to print out if the line contains Sub or contains Function.

 

[Micron]

This would probably be better

VBA – Listing all procedures in all modules – Useful code

 

[DavidJB]

I tried both solutions. The first produced a rather confusing output but it did seem to include the module number. However it only seemed to list the first sub in each module.
The second method worked when I replaced CreateLogFile strSubsInfo with Debug.Print strSubsInfo as suggested. This produced a listing of all the macros but did not include the name of the containing module for each macro. (I did download Createlogfile from your github and tried with CreateLogFile strSubsInfo but I got errors.)

It looks like my request is a bit more difficult than I was hoping. Still I can use the output from the second method and just manually add the module numbers so at least I have an listing of where the macros are.

Thanks for the suggestions

 

[Micron]

From that it would seem that combining code elements might produce the module names and all of the procedures in it?
The Access db documenter seems to be able to give both, albeit it produces a lot more than what you'd probably want (if not, then consider if you can add all your modules to an Access db project). At the very least, it seems to expose classes and properties of VB. In the pic, module name is at the top, content follows and outputs all procedures.
Then there is also more research to find exactly what you need, seeing as how I doubt you're the first to ask.

 

[DavidJB]

I might investigate that route when I have a bit of time. However I achieved what I wanted by adding an empty sub at the top of each module with the name e.g. Private Sub Module1(). That way the module name appears at the top of the listing of each module produced by Method 2. If I paste that listing into an Excel sheet and apply a bit of conditional formatting to all cells starting with "Module" I get a nicely formatted list of all my modules and the macros they contain. A bit labour intensive but I don't need to do it very often.