Displaying All Components in a VBA Project

The ShowComponents procedure, which follows , loops through each VBA component in the active workbook and writes the following information to a worksheet:

The component's name
The component's type
The number of lines of code in the code module for the component

 Sub ShowComponents()     Dim VBP As VBIDE.VBProject     Dim VBC As VBComponent     Dim row As Long     Set VBP = ActiveWorkbook.VBProject '   Write headers     Cells.ClearContents      Range("A1:C1") = Array("Name", "Type", "Code Lines")     Range("A1:C1").Font.Bold = True     row = 1 '   Loop through the VB Components     For Each VBC In VBP.VBComponents         row = row + 1 '       Name         Cells(row, 1) = VBC.Name '       Type         Select Case VBC.Type             Case vbext_ct_StdModule                 Cells(row, 2) = "Module"             Case vbext_ct_ClassModule                 Cells(row, 2) = "Class Module"             Case vbext_ct_MSForm                 Cells(row, 2) = "UserForm"             Case vbext_ct_Document                 Cells(row, 2) = "Document Module"         End Select '       Lines of code         Cells(row, 3) = VBC.CodeModule.CountOfLines     Next VBC End Sub

Notice that I used built-in constants (for example vbext_ct_StdModule ) to determine the component type. These constants are not defined unless you've established a reference to the Microsoft Visual Basic for Applications Extensibility Library.

Figure 28-3 shows the result of running the ShowComponents procedure. In this case, the VBA project contained seven components, and only one of them had a nonempty code module.

Figure 28-3: The result of executing the ShowComponents procedure.

CD-ROM

This workbook, named list VB components.xlsm , is available on the companion CD-ROM. Notice that it contains a reference to the VBA Extensibility Library.