Okay, I'm going to do this the hard way, because I'm going to assume that you don't want to have to download the VBE classes from my repository to make this a bit easier to work with, but they're there as an example of what's possible regardless.
First, you'll need to add a reference to the Microsoft Visual Basic for Applications Extensibility 5.3 Library and allow VBA to access the editor by taking the following steps. (Assumes Office 2010)
- File
- Options
- Trust Center
- Trust Center Settings
- Macro Settings
- Check "Trust access to the VBA project object model".
Now we're ready to explore the code in the workbook, but first, some things to remember about what we're looking for here.
- Functions
- More specifically, Public Functions
- In standard *.bas modules (class functions can't be UDFs).
- That don't have Option Private Module
The code below works on the active vba project, but could be modified to take one as a parameter. It works with the quick test cases I provided below the Run sub, but I wouldn't guarantee that it works for all corner cases. Parsing is hard. This also just stores and prints the function signatures in the results collection. I imagine in reality you would want a function that returns them, so that you could loop through the collection looking for them in the workbook.
Option Explicit
Private Sub Run()
Dim results As New Collection
Dim component As VBIDE.VBComponent
For Each component In Application.VBE.ActiveVBProject.VBComponents
If component.Type = vbext_ct_StdModule Then
' find public functions with no arguments
Dim codeMod As CodeModule
Set codeMod = component.CodeModule
If InStr(1, codeMod.Lines(1,codeMod.CountOfDeclarationLines), "Option Private Module") = 0 Then
Dim lineNumber As Long
lineNumber = codeMod.CountOfDeclarationLines + 1
Dim procName As String
Dim procKind As vbext_ProcKind
Dim signature As String
' loop through all lines in the module
While (lineNumber < codeMod.CountOfLines)
procName = codeMod.ProcOfLine(lineNumber, procKind) 'procKind is an OUT param
Dim lines() As String
Dim procLineCount As Long
procLineCount = codeMod.ProcCountLines(procName, procKind)
lines = Split(codeMod.lines(lineNumber, procLineCount), vbNewLine)
Dim i As Long
For i = 0 To UBound(lines)
If lines(i) <> vbNullString And Left(Trim(lines(i)), 1) <> "'" Then
signature = lines(i)
Exit For
End If
Next
' this would need better parsing, but should be reasonably close
If InStr(1, signature, "Public Function", vbTextCompare) > 0 Then 'first make sure we have a public function
results.Add signature
End If
lineNumber = lineNumber + procLineCount + 1 ' skip to next procedure
Wend
End If
End If
Next component
Dim str
For Each str In results
Debug.Print str
Next
End Sub
Public Function foo()
End Function
Private Function bar()
End Function
Public Function qwaz(duck)
End Function
