Programatically retrieve a list of public functions.
 

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

Programatically retrieve a list of public functions.
 

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

--
Regards,
Tom Ogilvy

"Alan Howells" wrote in message
...
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

Programatically retrieve a list of public functions.
 

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

"Alan Howells" wrote in message
...
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

Programatically retrieve a list of public functions.
 

Oops,

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

HTH,
Bernie


"Bernie Deitrick" wrote in message
...
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

"Alan Howells" wrote in message
...
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

Programatically retrieve a list of public functions.
 

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

"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy

"Alan Howells" wrote in message
...
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