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

Calculating Week-Ending Dates

When working with dates, you may need to figure out all the dates on which weeks end in a given year. There are several ...

Discover More

Deleting Freezes Computer

Sometimes a strange object or text may appear in your document, as happened to Sharon. To complicate the situation, her ...

Discover More

Applying Formatting in Lists

If you want to change the formatting applied to numbers or bullets in your lists, you'll appreciate the information in ...

Discover More

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!

More ExcelTips (ribbon)

Using R1C1 Formula References in a Macro

Besides the regular way of displaying formulas, Excel can also display them using what is called R1C1 format. If you are ...

Discover More

Maximum Length Limit for a Macro

Make your macros too long, and Excel may just refuse to run them at all. This tip explains what the limit is for macros, ...

Discover More

Putting an X in a Clicked Cell

Need to click on a cell and have it replaced with an "X"? Macros make it easy to do, as illustrated in this tip.

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 six more than 7?

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.