Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)
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.
| CD-ROM | This workbook, named |