• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 262
  • Last Modified:

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.

  • 4
  • 2
1 Solution
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.


gabrielPennybackAuthor 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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?


gabrielPennybackAuthor 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:




Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now