Displaying All Components in a VBA Project

The ShowComponents procedure, shown in Listing 28-1, 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

Listing 28-1: Displaying Each Active VBA Component in a Worksheet

Sub ShowComponents()

Dim VBP As VBProject Set VBP = ActiveWorkbook.VBProject NumComponents = VBP.VBComponents.Count Cells.ClearContents For i = 1 To NumComponents Name

Cells(i, 1) = VBP.VBComponents(i).Name Type

Select Case VBP.VBComponents(i).Type Case 1

Cells(i, 2) = "Document Module" End Select ' Lines of code

Cells(i, 3) = _ VBP.VBComponents(i).CodeModule.CountOfLines Next i End Sub

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

^ show components.xls

■TI51

3

A

B

C

D

E

T

1

ThisWorkbook

Document Module

0

2

Sbeetl

Document Module

0

3

Ivlodulel

Module

35

4

Classl

Class Module

0

5

UserForml

UserForm

0

G

7

8

3

10

-

► M \sheetl /

M 1

>IJ

G

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

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

This workbook is available on the companion CD-ROM. Notice that it contains a reference to the VBA Extensibility Library.

0 0

Post a comment