OfficeTalk: Display a List of All VBA Procedures in a Workbook from the Ribbon

Article
05/23/2014

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

Summary: See how to programmatically list all of the subroutines and functions in an Excel 2007 workbook. Create a user form that you display from a button on the Ribbon. (6 Printed Pages)

Frank Rice, Microsoft Corporation

June 2009

Applies to: Microsoft Office Excel 2007

Contents

Overview of the Project
Creating the User Form
Adding a Button to the Ribbon to Open the User Form
Testing the Project
Conclusion
Additional Resources

Overview of the Project

In this column, I demonstrate using XML and Microsoft Visual Basic for Applications (VBA) code to display a user form from a button on the Microsoft Office Fluent Ribbon user interface (UI). On the user form, you display a list of all of the VBA procedures in the Microsoft Office Excel 2007 workbook. You are also able to save the list of procedures to a text file.

You might say “What’s the big deal?” You can already see the procedures in a workbook from the Macros button on the Developers tab. However, only parameter-less subroutines in modules and within a worksheet are displayed in the Macros dialog box. Functions and subroutines with parameters do not show up. The code described in this column displays all of the subroutines and functions in the workbook.

Creating the User Form

The user form in this project consists of a list box and three buttons: a button to list the names of the container (module or class) and code names, a button to save the contents of the list box to a text file, and a button to close the form.

After making a backup copy, open an Excel 2007 workbook that contains some VBA procedures.
Next, you need to enable access to the VBA project. Click the Developer tab on the Ribbon and then click the Macro Security option in the Code group.

Note

If you do not see the Developers tab, click the Microsoft Office button, click Excel Options, click Popular, and then select the Show Developers tab in the Ribbon option.
On Macro Settings tab, select the Trust access to the VBA project object model option. Click OK.
On the Developers tab, click Visual Basic. The Visual Basic Editor is displayed.
Now set a reference to the VBA Extensibility library. The library contains the definitions of the objects that make up the VBProject. In the VBA editor, on the Tools menu and click References. In the References dialog, scroll down and select the entry for Microsoft Visual Basic for Applications Extensibility 5.3. Click OK.
On the Insert menu, click User Form. The User Form design surface is displayed.
On the View tab, click Toolbox. On the toolbox, drag a ListBox control onto the user form. Expand the ListBox horizontally to cover the top portion of the form. Expand the ListBox vertically to cover about ¾ of the form.
Next, drag three buttons onto the user form below the ListBox. Starting from left to right, set the properties shown in the following table and resize the buttons as necessary:

Table 1. Property names and captions

Property Name

Caption

btnList

List Procs

btnSave

Save to File

btnClose

Close

Property Name	Caption
btnList	List Procs
btnSave	Save to File
btnClose	Close

Double-click the btnList button and add the following code. This procedure uses the VBIDE class to gain access to the various components that comprise a VBA project.

    ' Declare variables to access the Excel 2007 workbook.
    Dim objXLApp As Excel.Application
    Dim objXLWorkbooks As Excel.Workbooks
    Dim objXLABC As Excel.Workbook

    ' Declare variables to access the macros in the workbook.
    Dim VBAEditor As VBIDE.VBE
    Dim objProject As VBIDE.VBProject
    Dim objComponent As VBIDE.VBComponent
    Dim objCode As VBIDE.CodeModule

    ' Declare other miscellaneous variables.
    Dim iLine As Integer
    Dim sProcName As String
    Dim pk As vbext_ProcKind

    Set VBAEditor = Application.VBE

    ' Open Excel and the open the workbook.
    Set objXLApp = New Excel.Application

    ' Empty the list box.
    ListBox1.Clear

    ' Get the project details in the workbook.
    Set objProject = VBAEditor.ActiveVBProject

    ' Iterate through each component in the project.
    For Each objComponent In objProject.VBComponents

        ' Find the code module for the project.
        Set objCode = objComponent.CodeModule

        ' Scan through the code module, looking for procedures.
        iLine = 1
        Do While iLine < objCode.CountOfLines
            sProcName = objCode.ProcOfLine(iLine, pk)
            If sProcName <> "" Then
                ' Found a procedure. Display its details, and then skip
                ' to the end of the procedure.
                ListBox1.AddItem objComponent.Name & ": " & sProcName
                iLine = iLine + objCode.ProcCountLines(sProcName, pk)
            Else
                ' This line has no procedure, so go to the next line.
                iLine = iLine + 1
            End If
        Loop
        Set objCode = Nothing
        Set objComponent = Nothing
    Next

    ' Clean up and exit.
    Set objProject = Nothing
    objXLApp.Quit

Double-click the btnSave button and add the following code. This procedure uses the File System Object to create a folder and text file to contain the procedure listing from the user form.

On Error Resume Next

Dim objFSO, fldr As Variant
Dim i As Integer
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set fldr = objFSO.CreateFolder("C:\MyTest")
Set txtfile = objFSO.CreateTextFile("C:\MyTest\testfile.txt", True)

For i = 0 To ListBox1.ListCount - 1
    txtfile.Write (ListBox1.List(i)) & vbCrLf
Next

txtfile.Close

And finally, double-click the btnClose button and add the following statement.
```
Unload Me
```

Adding a Button to the Ribbon to Open the User Form

In the following steps, you add a tab and button to the workbook that displays the user form. To modify the Ribbon, you need to add XML that defines the structure and components of the new tab. You also need to add a callback procedure that opens the user form when you click the button.

To get the XML into the workbook, add a file to the workbook package. Microsoft Office Excel 2007 workbook files (.xlsx and .xlsm) are Office Open XML packages using the industry standard Zip technology to contain the parts that make up a file. You can see the files that comprise the workbook by adding the .zip extension to the name of the file and double-clicking the file. In the following steps, you add the XML file that adds the custom tab and button to the workbook.

Rename the Excel 2007 workbook file by appending the .zip extension to the filename and then double-click to open the file.
On the Windows Desktop, create a folder named customUI.

Open NotePad and add the following XML:

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
   <ribbon startFromScratch="false">
     <tabs>
       <tab id="rxtab" insertBeforeMso="TabHome" label="Show Macros">
         <group id="rxgrp" label="Show Macro List">
             <button id="rxbtn" imageMso="RefreshStatus" label="Display List" onAction="RunUserForm"/>
        </group>
      </tab>
     </tabs>
   </ribbon>
</customUI>

Close and save the file as customUI.xml in the customUI folder. Drag the folder onto the Zip package.
Next, link the customUI folder to the rest of the package. To do this, you update a relationship part that defines links between the other parts in the package. In the Zip container, double-click the _rels folder and then drag the .rels file onto the Windows Desktop.

Open the .rels file in NotePad. Just above the </Relationships> element, add the following statement:

<Relationship Id="R7e0fdb8abcd34810" Type="http://schemas.microsoft.com/office/2006/relationships/ui/extensibility" Target="customUI/customUI.xml"/>

Save and close the file. Drag the updated file back to the _rels folder in the package.

Testing the Project

In these next steps, you test the project by opening the user form, displaying the list of VBA procedures in the project, and saving the list to a text file.

Remove the .zip extension from the file name.
Open the file in Excel 2007 and then click the Show Macros tab.
Click Display List.
In the user form, click List Procs. The list of VBA procedures is displayed. A sample display is shown in Figure 1.

Figure 1. A sample showing the VBA procedures listed in the user form
Next, click Save to File. Navigate to the C:\MyTest folder (or the folder you specified in the code) and open the testfile.txt file. The file contains the list of procedures from the user form.

Conclusion

The advantage in using the code in this sample project instead of the built-in Macros button on the Developers tab is that all of the subroutines and functions in the workbook are displayed. You should experiment some more with the VBIDE class to see the other things you can do with the VBA code in your workbook. For example, you can programmatically add modules and procedures to a project, remove modules and procedures from a project, save all of the procedure code to a text file, and much more.

Additional Resources

More information on the topics discussed in this column is available at the following locations.