Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
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
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.