Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, and 2016. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Generating a List of Macros.

Generating a List of Macros

by Allen Wyatt
(last updated January 20, 2018)

7

Andrew has a workbook that has a good number of macros in it. He would like to generate a list of all the macros it contains, so he wonders if there is a way to accomplish the task.

There is no intrinsic way within Excel to create a list of macros. You can, however, create a macro that will list your macros. (Sort of sounds redundant, doesn't it?)

As an example, consider the following macro, which steps through all the projects in your workbook to garner all the macro names and place them in a worksheet:

Sub ListMacros()
    Dim VBComp As VBComponent
    Dim wsTarget As Worksheet
    Dim StartLine As Long
    Dim iRow As Integer

    Application.ScreenUpdating = False

    Set wsTarget = Worksheets.Add
    wsTarget.Range("A1") = "Macro"
    wsTarget.Range("A1").Font.Bold = True
    With wsTarget.Range("A1").Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With

    iRow = 2

    For Each VBComp In ThisWorkbook.VBProject.VBComponents
        With VBComp.CodeModule
            StartLine = .CountOfDeclarationLines + 1
            Do Until StartLine >= .CountOfLines
                wsTarget.Cells(iRow, 1) = _
                  .ProcOfLine(StartLine, vbext_pk_Proc)
                iRow = iRow + 1
                StartLine = StartLine + _
                  .ProcCountLines(.ProcOfLine(StartLine, _
                  vbext_pk_Proc), vbext_pk_Proc)
            Loop
        End With
    Next VBComp

    wsTarget.Range("A1").EntireColumn.AutoFit
    Application.ScreenUpdating = True
End Sub

In order to use this macro, you must make sure you have the Microsoft VBA extensibility reference set. To do this, follow these steps:

  1. In the VBA Editor, choose References from the Tools menu. The References dialog box is displayed.
  2. Scroll through the list of Available References and make sure the Microsoft Visual Basic for Applications Extensibility check box is selected. (There may be a version number at the end of the reference, but it should begin with the words noted.) (See Figure 1.)
  3. Figure 1. The References dialog box.

  4. Close the dialog box.

You'll also need to make a quick change in your Trust Center settings. Follow these steps:

  1. Display the Developer tab of the ribbon.
  2. In the Code group, click the Macro Security tool. Excel displays the Trust Center dialog box with the Macro Settings options selected at the left. (See Figure 2.)
  3. Figure 2. The Trust Center dialog box.

  4. Make sure the Trust Access to the VBA Project Object Model option is selected.
  5. Click OK.

When you run the macro, it adds a new worksheet to your workbook, and then lists the names of all the macros in all the modules in the workbook.

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (5225) applies to Microsoft Excel 2007, 2010, 2013, and 2016. You can find a version of this tip for the older menu interface of Excel here: Generating a List of Macros.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Removing a List

If you have lists in your document, either bulleted or numbered, you may want to change them back to regular text at some ...

Discover More

Determining If a Value is Out of Limits

Need to figure out if a value is outside of some arbitrary limit related to a different value? There are a number of ways ...

Discover More

Naming Tabs for Weeks

Need to set up a workbook that includes a worksheet for each week of the year? Here's a couple of quick macros that can ...

Discover More

Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!

More ExcelTips (ribbon)

Finding the Last-Used Cell in a Macro

Ever wonder what the macro-oriented equivalent of pressing Ctrl+End is? Here's the code and some caveats on using it.

Discover More

Retrieving Drive Statistics

Need to gather some information about the drives on a system? It can be pretty easy to do using a macro, as shown in this ...

Discover More

Turning Off Screen Updating

Want a quick way to speed up your macros? All you need to do is to stop Excel from updating the screen while the macro is ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is four more than 2?

2018-01-28 12:06:14

Walter

Peter

Thank you very, very much for going the extra mile and making this a very, very useful macro.

I 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.

I'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.


2018-01-27 10:56:05

Peter Atherton

This is a really useful macro - Kudos to Allen!!

I 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.


Option Explicit

Sub ListMacros()
Dim VBComp As VBComponent
Dim wsTarget As Worksheet, wks As Worksheet
Dim wkb As Workbook
Dim StartLine As Long
Dim iRow As Integer

With Application
.ScreenUpdating = False
.DisplayAlerts = False
End With
Set wkb = ActiveWorkbook
With wkb
For Each wks In ActiveWorkbook.Sheets
If wks.Name = "ListMacros" Then
wks.Delete
End If
Next
Set wsTarget = Worksheets.Add
wsTarget.Name = "ListMacros"
wsTarget.Range("A1") = "Modules' Name"
wsTarget.Range("b1") = "Macros' Name"
wsTarget.Range("A1:B1").Font.Bold = True
With wsTarget.Range("A1:b1").Borders(xlEdgeBottom)
.LineStyle = xlContinuous
End With

iRow = 2

For Each VBComp In wkb.VBProject.VBComponents
With VBComp.CodeModule
StartLine = .CountOfDeclarationLines + 1
Do Until StartLine >= .CountOfLines
wsTarget.Cells(iRow, 1) = VBComp.CodeModule.Name
wsTarget.Cells(iRow, 2) = _
.ProcOfLine(StartLine, vbext_pk_Proc)
iRow = iRow + 1
StartLine = StartLine + _
.ProcCountLines(.ProcOfLine(StartLine, _
vbext_pk_Proc), vbext_pk_Proc)
Loop
End With
Next VBComp

wsTarget.Range("A1:b1").EntireColumn.AutoFit
With Application
.ScreenUpdating = True
.DisplayAlerts = True
End With

End With
End Sub


2018-01-21 11:00:49

Peter Atherton

Walter

add this line above the macro name (after the do loop

wsTarget.Cells(iRow, 1) = VBComp.CodeModule.Name
and change the code name column to 2


2018-01-20 16:28:18

Walter

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.

Is this left as an exercise for the student?

Or, will there be a part two next week?

Or, if it is easy enough, could someone please extend the macro so that we can also quickly located in its module?


2018-01-20 16:19:26

Walter

@Stefan

I 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.


2018-01-20 11:31:59

Sheldon Hopkins

In the January 20th Excel Tips was a "Generating a List of Macros".

I'm running Office 365 and have LOTS of Macros in an Excel Workbook with 12 Worksheets.and at least 66 Macros.
I have 'discovered' the ability to list the Macros in VBA which can be "stepped into" or "edited".
This is quite nice because I have nearly 100 printed pages of VBA. When trying to find a specific Macro:
------
With VBA on the monitor...
Across the top line you will find "Tools" -- click on it.
In the drop-down list you will find "Macros" -- click on it. You will find a list of all Macros in the Workbook.
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.)
------
[ I can't validate that ALL macros are listed; it would simply take more time to compare lists than it is worth. ]

I wish there was a similar facility for listing 'controls' as there likely over 250 'buttons' within the Workbook.


2018-01-20 11:17:14

stefan

i get the error, "User-defined type not defined" referring to the statement: Dim VBComp As VBComponent

i've done everything else!


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.