Hi there

Having written a large number of UDFs and Classes with their
associated Properties & Methods I was wondering if rather than
manually typing them all into a spreadsheet is there a way I can get
VBA to do this automatically for me?

So for example, the code would search through the current open
projects, note the various functions & subs in Modules and Class
Modules and list them all on a new sheet.

I'm sure this would be quite useful for a lot of people.

Many Thanks
Lloyd

Lloyd,

Try the macro below. It requires a reference to MS VBA Extensibility.

HTH,
Bernie
MS Excel MVP

Sub ListFunctionsAndSubs()

Dim myBook As Workbook

Dim myStartLine As Long
Dim NumLines As Long

Dim ProcName As String
Dim VBComp As VBComponent
Dim RowNdx As Long

Cells(1, 1).Value = "Workbook Name"
Cells(1, 2).Value = "Module Name"
Cells(1, 3).Value = "Procedure Name"
Cells(1, 4).Value = "Type"
Cells(1, 5).Value = "Start Line"
Cells(1, 6).Value = "Number of Lines"

RowNdx = 2
For Each myBook In Application.Workbooks
For Each VBComp In myBook.VBProject.VBComponents
If VBComp.Type = vbext_ct_StdModule Then
NumLines = 0
With VBComp.CodeModule
myStartLine = .CountOfDeclarationLines + 1
While myStartLine + NumLines < .CountOfLines
ProcName = .ProcOfLine(myStartLine, vbext_pk_Proc)
Cells(RowNdx, 1).Value = myBook.Name
Cells(RowNdx, 2).Value = VBComp.Name
Cells(RowNdx, 3).Value = ProcName
If .Find("Fun", myStartLine, 1, myStartLine, 100, False, False, False) Then
myType = "Function"
Else: myType = "SubRoutine"
End If
Cells(RowNdx, 4).Value = myType
Cells(RowNdx, 5).Value = myStartLine
NumLines = .ProcCountLines(ProcName, vbext_pk_Proc)
Cells(RowNdx, 6).Value = NumLines
myStartLine = myStartLine + NumLines
RowNdx = RowNdx + 1
Wend
End With
End If

Next VBComp
Next myBook
End Sub




"pinkfloydfan" wrote in message
ups.com...
Hi there

Having written a large number of UDFs and Classes with their
associated Properties & Methods I was wondering if rather than
manually typing them all into a spreadsheet is there a way I can get
VBA to do this automatically for me?

So for example, the code would search through the current open
projects, note the various functions & subs in Modules and Class
Modules and list them all on a new sheet.

I'm sure this would be quite useful for a lot of people.

Many Thanks
Lloyd

Thanks Bernie that's cool!

Now as I am not familiar with what the VBA Extensibility does or how
to use the Type VBComponent...how do we improve this to cover
ClassModules and list their various parts..?

Cheers
Lloyd

Lloyd,

You will need to expand this part:

If .Find("Fun", myStartLine, 1, myStartLine, 100, False, False, False) Then
myType = "Function"
Else
myType = "SubRoutine"
End If

to include items that you want to look for, like Property Let and Property Get statements. "That is
left as an exercise for the student..." ;-)

HTH,
Bernie
MS Excel MVP


Sub ListFunctionsAndSubs2()

Dim myBook As Workbook

Dim myStartLine As Long
Dim NumLines As Long

Dim ProcName As String
Dim VBComp As VBComponent
Dim RowNdx As Long

On Error Resume Next

Cells(1, 1).Value = "Workbook Name"
Cells(1, 2).Value = "Module Name"
Cells(1, 3).Value = "Module Type"
Cells(1, 4).Value = "Procedure Name"
Cells(1, 5).Value = "Type"
Cells(1, 6).Value = "Start Line"
Cells(1, 7).Value = "Number of Lines"

RowNdx = 2
For Each myBook In Application.Workbooks
For Each VBComp In myBook.VBProject.VBComponents
If VBComp.Type = vbext_ct_StdModule Or VBComp.Type = vbext_ct_ClassModule Then
NumLines = 0
With VBComp.CodeModule
myStartLine = .CountOfDeclarationLines + 1
While myStartLine + NumLines < .CountOfLines
ProcName = .ProcOfLine(myStartLine, vbext_pk_Proc)
Cells(RowNdx, 1).Value = myBook.Name
Cells(RowNdx, 2).Value = VBComp.Name
Cells(RowNdx, 3).Value = IIf(VBComp.Type = vbext_ct_ClassModule, "Class Mod", "Std
Mod")
Cells(RowNdx, 4).Value = ProcName
If .Find("Fun", myStartLine, 1, myStartLine, 100, False, False, False) Then
myType = "Function"
Else
myType = "SubRoutine"
End If
Cells(RowNdx, 5).Value = myType
Cells(RowNdx, 6).Value = myStartLine
NumLines = .ProcCountLines(ProcName, vbext_pk_Proc)
Cells(RowNdx, 7).Value = NumLines
myStartLine = myStartLine + NumLines
RowNdx = RowNdx + 1
Wend
End With
End If

Next VBComp
Next myBook
End Sub

--
HTH,
Bernie
MS Excel MVP


"pinkfloydfan" wrote in message
ps.com...
Thanks Bernie that's cool!

Now as I am not familiar with what the VBA Extensibility does or how
to use the Type VBComponent...how do we improve this to cover
ClassModules and list their various parts..?

Cheers
Lloyd

See www.cpearson.com/excel/vbe.htm#ListAllInProject and
www.cpearson.com/excel/vbe.htm#ProcDeclaration for example code to list all
procedures, methods, and properties of objects defined in a project, and to
list the declarations of properties and methods.



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)



"pinkfloydfan" wrote in message
ups.com...
Hi there

Having written a large number of UDFs and Classes with their
associated Properties & Methods I was wondering if rather than
manually typing them all into a spreadsheet is there a way I can get
VBA to do this automatically for me?

So for example, the code would search through the current open
projects, note the various functions & subs in Modules and Class
Modules and list them all on a new sheet.

I'm sure this would be quite useful for a lot of people.

Many Thanks
Lloyd

There's also the MZ-Tools addin, which has a number of Utilities for
analysing your code, especially the pretty detailed "Generate XML
Documentation".
http://www.mztools.com/index.htm

NickHK

"pinkfloydfan" wrote in message
ups.com...
Hi there

Having written a large number of UDFs and Classes with their
associated Properties & Methods I was wondering if rather than
manually typing them all into a spreadsheet is there a way I can get
VBA to do this automatically for me?

So for example, the code would search through the current open
projects, note the various functions & subs in Modules and Class
Modules and list them all on a new sheet.

I'm sure this would be quite useful for a lot of people.

Many Thanks
Lloyd

Thanks everybody this has been really helpful