Creating a list of all macros in a workbook.

Posted on 2012-03-26
Last Modified: 2012-03-31
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.

Question by:gabrielPennyback
  • 4
  • 2
LVL 41

Expert Comment

ID: 37768295
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()


LVL 41

Accepted Solution

dlmille earned 500 total points
ID: 37769688
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.



Author Comment

ID: 37772427
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

LVL 41

Expert Comment

ID: 37772486
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?



Author Comment

ID: 37773511
I'll put something together and upload it, thanks.
LVL 41

Expert Comment

ID: 37792052
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

ScreenConnect 6.0 Free Trial

Want empowering updates? You're in the right place! Discover new features in ScreenConnect 6.0, based on partner feedback, to keep you business operating smoothly and optimally (the way it should be). Explore all of the extras and enhancements for yourself!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article is a follow-up to my Mappit! Addin Article (, it was inspired by an email posting I made to EUSPRIG (, I will briefly cover: 1) An overvie…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question