Programatically retrieve a list of public functions.

[Alan Howells]

Morning all

Is there anyway to retrieve a list of public functions from a workbook. Or
in other words how does the function wizard get it's list of user-defined
functions from open workbooks.

Thanks in advance,


Alan

 

[Tom Ogilvy]

Excel does this internally and does not expose this list. If you want to
get a list, you would need to parse through all you code modules.

You can start by looking at this page on Chip Pearson's site:

http://www.cpearson.com/excel/vbe.htm

 

[Bernie Deitrick]

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

 

[Bernie Deitrick]

Oops,

Forgot my standard note about the sub requiring a reference to MS VBA
extensibility: in the VBEditor, Tools | Referencees...

HTH,
Bernie

 

[Alan Howells]

Admit, I was hoping not to do that. Oh well, thanks for your responses.