Alan,
Below is a sub that shows how to do it. The sub needs a workbook
passed to it, so run it with another sub, along the lines of:
Sub ListFunctionsInWorkbook()
ListPublicFunctionsOnly ThisWorkbook
End Sub
HTH,
Bernie
Sub ListPublicFunctionsOnly(myBook As Workbook)
Dim StartLine As Long
Dim NumLines As Long
Dim TheLine As String
Dim ProcName As String
Dim VBComp As VBComponent
Dim myMsg As String
Dim i As Long
myMsg = "Public functions in " & myBook.Name & ":" & Chr(10)
For Each VBComp In myBook.VBProject.VBComponents
If VBComp.Type = vbext_ct_StdModule Then
With VBComp.CodeModule
StartLine = .CountOfDeclarationLines + 1
While StartLine + NumLines < .CountOfLines
ProcName = .ProcOfLine(StartLine, vbext_pk_Proc)
NumLines = .ProcCountLines(ProcName, vbext_pk_Proc)
For i = StartLine To StartLine + NumLines - 1
TheLine = .Lines(i, 1)
If InStr(1, TheLine, "Public Function", vbTextCompare) = 1 Then
myMsg = myMsg & "Mod " & VBComp.Name & " - " & ProcName & "()" &
Chr(10)
End If
Next i
StartLine = StartLine + NumLines
Wend
End With
End If
Next VBComp
MsgBox myMsg
End Sub