Programatically retrieve a list of public functions.

  • Thread starter Thread starter Alan Howells
  • Start date Start date
A

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
 
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
 
Oops,

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

HTH,
Bernie
 
Back
Top