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.

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.

1. After making a backup copy, open an Excel 2007 workbook that contains some VBA procedures.

2. 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.

3. On Macro Settings tab, select the Trust access to the VBA project object model option. Click OK.

4. On the Developers tab, click Visual Basic. The Visual Basic Editor is displayed.

5. 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.

6. On the Insert menu, click User Form. The User Form design surface is displayed.

7. 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.

8. 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

9. 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.

   Visual Basic

       ' 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

10. 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.

    Visual Basic

    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

11. And finally, double-click the btnClose button and add the following statement.

    Unload Me

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.

1. Rename the Excel 2007 workbook file by appending the .zip extension to the filename and then double-click to open the file.

2. On the Windows Desktop, create a folder named customUI.

3. Open NotePad and add the following XML:

   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>

4. Close and save the file as customUI.xml in the customUI folder. Drag the folder onto the Zip package.

5. 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.

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

   Xml

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

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

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.

1. Remove the .zip extension from the file name.

2. Open the file in Excel 2007 and then click the Show Macros tab.

3. Click Display List.

4. 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
   
   

5. 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.

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.

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

• Programming The VBA Editor

• Customizing the 2007 Office Fluent Ribbon for Developers (Parts 1 through 3)