Generating a List of Macros

by Allen Wyatt
(last updated June 1, 2013)

Once you start writing Excel macros, it is easy to get quite a few of them in a workbook. At some point you may want to generate a list of macros in your workbook. 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 VBCodeMod As CodeModule
    Dim oListsheet As Object
    Dim StartLine As Long
    Dim ProcName As String
    Dim iCount As Integer

    Application.ScreenUpdating = False
    On Error Resume Next
    Set oListsheet = ActiveWorkbook.Worksheets.Add
    iCount = 1
    oListsheet.[a1] = "Macro"

    For Each VBComp In ThisWorkbook.VBProject.VBComponents
        Set VBCodeMod = ThisWorkbook.VBProject.VBComponents(VBComp.Name).CodeModule
        With VBCodeMod
            StartLine = .CountOfDeclarationLines + 1
            Do Until StartLine >= .CountOfLines
                oListsheet.[a1].Offset(iCount, 0).Value = _
                  .ProcOfLine(StartLine, vbext_pk_Proc)
                iCount = iCount + 1

                StartLine = StartLine + _
                  .ProcCountLines(.ProcOfLine(StartLine, _
                   vbext_pk_Proc), vbext_pk_Proc)
            Loop
        End With
        Set VBCodeMod = Nothing
    Next VBComp

    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. (See Figure 1.)
  2. Figure 1. The References dialog box.

    ***Insert Figure X –
  3. Scroll through the list of Available References and make sure the Microsoft Visual Basic for Applications Extensibility check box is selected.
  4. Close the dialog box.

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.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2715) applies to Microsoft Excel 97, 2000, 2002, and 2003.

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

Formatting All Headings At Once

If you need to apply a common formatting change to all the headings in your document, a quick way to do it is to use the ...

Discover More

Understanding SmartArt

SmartArt provides a way to add classy presentation graphics to your document. Here's a high-level explanation of what you can ...

Discover More

Modifying the Color Scheme

Windows maintains what it calls a color scheme to control how the various elements of Windows appear. Here's how to change ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

MORE EXCELTIPS (MENU)

Clean Up Your Macro List

Got a workbook cluttered with all sorts of macros? Delete them and you'll make your workbook easier to manage.

Discover More

Develop Macros in Their Own Workbook

If you develop macros and edit them quite a bit, you may be running the risk of causing problems with the macros or with your ...

Discover More

Disabling Shift Key Use when Opening a Workbook

Open up a workbook, and Excel normally runs the macros associated with that workbook. You can disable the automatic running ...

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 for this tip:

There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)

This Site

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

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.

Links and Sharing
Share