documenting all UDFs in an XLA

[Smallweed]

Hi, I've inherited a vast add-in and would like to list all UDFs in it. Does
anyone know of a way to do this? If possible, I'd like to make a distinction
between visible functions and ones the user doesn't see in the Function
Wizard/Paste Function (i.e. ones that are public as opposed to private).

It would also be great to then document these UDFs alongside the internal
functions they each call - that is, create a kind of tree so I can then try
to audit this thing! That may be a tall order...

Any help appreciated.

 

[John Bundy]

This should help you out:
http://www.cpearson.com/excel/vbe.aspx

 

[Smallweed]

Thanks very much John - a very useful site for VBA Extensibility code.

In case anyone else is doing what I'm doing, I've adapted the "Listing All
Procedures In A Module" code so that it documents all modules in a
workbook/xla(I've included a minor fix - you'll see a "+ 1" has been remmed
out as I found this was rolling forward an unnecessary line each time which
finally resulted in some procedures being omitted. I'm not sure the ProcKind
is being completely dealt with either - I didn't need to worry as I had no
Property Let/Set/Gets I needed to document):

Sub ListProceduresForAllModules()
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule
Dim LineNum As Long
Dim NumLines As Long
Dim WS As Worksheet
Dim Rng As Range
Dim ProcName As String
Dim ProcKind As VBIDE.vbext_ProcKind
Dim intCtr As Integer

intCtr = 1
Set VBProj = Workbooks("my workbook/xla").VBProject
Set WS = Workbooks("my reporting workbook").Worksheets("Sheet1")
Set Rng = WS.Cells(intCtr, 1)

For Each VBComp In VBProj.VBComponents
Set CodeMod = VBComp.CodeModule
With CodeMod
LineNum = .CountOfDeclarationLines + 1
ProcName = .ProcOfLine(LineNum, ProcKind)
Do Until LineNum >= .CountOfLines
Rng(1, 1).Value = ProcName
Rng(1, 2).Value = ProcKindString(ProcKind)
Rng(1, 3).Value = VBComp.Name
intCtr = intCtr + 1
Set Rng = WS.Cells(intCtr, 1)
LineNum = LineNum + .ProcCountLines(ProcName, ProcKind) '+ 1
ProcName = .ProcOfLine(LineNum, ProcKind)
Loop
End With
Next
End Sub

Function ProcKindString(ProcKind As VBIDE.vbext_ProcKind) As String
Select Case ProcKind
Case vbext_pk_Get
ProcKindString = "Property Get"
Case vbext_pk_Let
ProcKindString = "Property Let"
Case vbext_pk_Set
ProcKindString = "Property Set"
Case vbext_pk_Proc
ProcKindString = "Sub Or Function"
Case Else
ProcKindString = "Unknown Type: " & CStr(ProcKind)
End Select
End Function