Creating a list of all macros in a workbook.

I would like to create a list of macros in my workbook, based on something like this:

Dim ListPos As Range, macroName As String
Set ListPos = cells([ListTop], 1).End(xlDown).Offset(1, 0)
ListPos = macroName

Of course, this may be not at all the way to go. But if it is, the two big questions are:
     1) How do I define MacroName to be the name of each macro in succession
     2) How do I set up the loop

i should be able to figure out the second one myself, but I can't.

John CarneyReliability Business Tools Analyst IIAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

You're going to need code like this and perhaps 15-30+ minutes of an Expert's time - assuming that Expert has gone here, before (I'm off to a meeting, but will check back in a bit):

Do you want all public modules, class modules, including Sheets and ThisWorkbook?

So, output would be something like:

Module   MacroType   MacroName
Module1  Function     getsomething()


John - I did a bit more than you asked, but felt this would be a useful utility as well.  As such, I created this file with some add-in menu items so you can run the macro against any active workbook.  

The actual code creates an Output Tab called "Enumerated_Macros" and details whatever it finds in that workbook in the form of Module, Procedure, Type - that way any Sheet or ThisWorkbook, Public Module or Class Module might be listed if it exists.

Here's the primary code:
Sub generateOutput()
Dim wkb As Workbook
Dim wks As Worksheet
Dim r As Range
Dim rng As Range
Dim i As Long
Dim vSubDetail As Variant

    Set wkb = ActiveWorkbook
    On Error Resume Next
    Set wks = wkb.Worksheets("Enumerated_Macros")
    If Err.Number <> 0 Then
        Set wks = wkb.Worksheets.Add(after:=wkb.Worksheets(wkb.Worksheets.Count))
        wks.Name = "Enumerated_Macros"
    End If
    On Error GoTo 0
    'clear the output sheet
    'get subs from every module in the workbook
    For Each vSubDetail In getSubsFromProject(ActiveWorkbook)
        wks.Range("A2").Offset(i, 0).Resize(, 3).Value = Split(vSubDetail, ",")
        i = i + 1
    Next vSubDetail
    'make it pretty
    With wks.Range("A1:C1")
        .Value = Split("Code Module, Procedure Name, Procedure Type", ",")
        .Font.Bold = True
    End With
    ActiveWindow.FreezePanes = False
    ActiveWindow.FreezePanes = True

End Sub

Open in new window

See attached workbook and if you like it you can actually save it as an add-in, always there for you.


For your specific request, you can use the call to getSubsFromProject(ActiveWorkbook) to achieve the end you are looking for.  If you just want that, copy over the module called VBA_Helper which synchronizes the VBE Editor and has the functions needed to make this all work.

Here's how I would write your code:
Sub getMyMacros()
Dim ListPos As Range, macroName As String
Dim vSubDetail As Variant '0-module name, 1-sub/function name, 2-sub/function type

    For Each vSubDetail In getSubsFromProject(ActiveWorkbook)
        Set ListPos = Cells(Rows.Count, [ListTop].Column).End(xlUp).Offset(1, 0)
        ListPos = Split(vSubDetail, ",")(1) 'get the sub name
    Next vSubDetail
End Sub

Open in new window

Note, your statement:

Set ListPos = cells([ListTop], 1).End(xlDown).Offset(1, 0)

Would result in an error if there were not other values below the ListTop range (end down would hit the bottom of the worksheet, and the Offset(1,0) would blow it out).

See attached.



Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
John CarneyReliability Business Tools Analyst IIAuthor Commented:
You know everyone once in a while I start thinking of myself as somewhat of an expert in VBA (the people I work with fortunately don't know any better), and then I encounter something like your workbook and that snaps me back into reality! :- )

Thanks, Dave, this is quite amazing.

Now come the part where i look the gift horse in the mouth and ask for one more thing, my reason for asking the question in the first place was that there are about 50 macros nested and sub-nested inside my main macro and I wanted something that would add the name of each macro to a list at the start of each macro.

So my final question would be: is there a generic macro that will automatically add its name to the list? As opposed to repeating the code 50 times, one for each macro like this:

Sub StampOpen()
[MacroList].End(xlDown).Offset(1, 0) = "StampOpen"
[start] = Now()
Dim str As String
str = Environ("Username")
[UserStamp] = "Run by " & Application.VLookup(str, Range("Users"), 2, False) & _
    " @ " & Format(Now, "h:mm AM/PM") & " on " & Format(Now, "m/d/yyyy")
End Sub

Something that would say: [MacroList].End(xlDown).Offset(1, 0) = thisMacroName

If this requires more than a moment's thought for you please let me know and I'll post it as a new question.

In the meantime, thanks for this awesome macro and workbook!

- John
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

Thanks for your kind words.  I, unfortunately, don't seem to have command of the English language as I'm having a hard time understanding the question.

Perhaps if you uploaded a sample workbook with sample output?


John CarneyReliability Business Tools Analyst IIAuthor Commented:
I'll put something together and upload it, thanks.
So, you have a macro that calls other macros and you want a list of the macro that each macro calls?


Sub Main()

  call Macro1

  call macro2

End Sub

Sub Macro1
  Call Macro3
End Sub
Sub Macro2
End sub
Sub Macro3
End Sub

Then you want in your sheet something like:



It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.