Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. 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

Written by Allen Wyatt (last updated January 31, 2022)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365


11

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, 2016, 2019, and Excel in Microsoft 365. 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

Limiting Searching to a Column

When you use Find and Replace, Excel normally looks through all the cells in a worksheet. You may want to limit the ...

Discover More

Copying a Range of Pages in a Macro

Do you need to copy, within a macro, a range of pages? Because pages can be so fluid in Word, this can be a bit tricky. ...

Discover More

Inserting a Dynamic Line Count

Word provides fields that allow you to insert a variety of informational items about your document into the document ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

More ExcelTips (ribbon)

Worksheet Events

You can create macros that are automatically executed whenever certain events occur within a worksheet. This tip details ...

Discover More

DOS from Macros

Need to run a DOS command from within one of your macros? The answer is the Shell command, described in this tip.

Discover More

Renaming Worksheets Based On a List

Renaming a worksheet within a macro is a relatively easy task. When you start renaming based on a range of names, though, ...

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}] (all 7 characters, in the sequence shown) 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 five more than 3?

2022-05-09 12:02:48

J. Woolley

Re. the Tip's ListMacros procedure, if you substitute procKind for each instance of vbext_pk_Proc as recommended in my previous comment and change the declaration of VBComp as follows
    Dim VBComp As Object 'not VBComponent
then you don't need to reference the Microsoft Visual Basic for Applications Extensibility library.
Also, you should change ThisWorkbook to ActiveWorkbook as suggested by Peter.
See http://www.cpearson.com/excel/vbe.aspx


2022-05-08 09:42:48

J. Woolley

@Kerry and Peter
To accommodate VBA procedures that are neither Sub nor Function, add the following statement to the Tip's ListMacros:
Dim procKind as Long
Then substitute procKind for each instance of vbext_pk_Proc (there are 3 instances).


2022-05-07 10:41:46

J. Woolley

My Excel Toolbox includes the following dynamic array function:
=ListProcs([SkipHeader])
This function returns the following 6 columns for each VBA procedure in the workbook:
Component (name), Type (module or user form), Procedure (name), Type (Sub or Function), Scope (Public or Private), Declaration. An example Declaration follows:
Public Function ListProcs(Optional SkipHeader As Boolean = False) As Variant()
In older versions of Excel you can use ListProcs with the SpillArray function like this:
=SpillArray(ListProcs([SkipHeader])
See https://sites.google.com/view/MyExcelToolbox/


2022-05-06 04:18:18

Kerry

Hi Allen,
I 've used this code in 2 workbooks, but it only works on 1. The excel settings are obviously identical.including the references in tools specific to the active workbook.

Only one workbook open at a time.

the code stops at

StartLine = StartLine + .ProcCountLines(.ProcOfLine(StartLine, _
vbext_pk_Proc), vbext_pk_Proc)

error is "sub or function not defined"

when I check the worksheet that contains the list, it as stopped at code in a form. I don't know if that is relevant or not.

this function is SO handy. what could be the problem. it works fine in another workbook


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.