PDA

View Full Version : How to determine #/type of function arguments via CODE



nitt1995
06-23-2006, 06:43 AM
I recently learned (via this site) how to list all of the functions/subs in a given module. Now I want to learn how determine the required number and type of arguments for these functions/subs. Any ideas out there?

Thanks

Zack Barresse
06-23-2006, 09:32 AM
Have you thought about MZ-Tools? http://www.vbaexpress.com/resources.php#Excel

Bob Phillips
06-23-2006, 10:37 AM
I recently learned (via this site) how to list all of the functions/subs in a given module. Now I want to learn how determine the required number and type of arguments for these functions/subs. Any ideas out there?

Thanks

I presume that the code you have parses the module text looking for Sub and Function to get the names. Just extend the parsing to look after the (. The Type will be after As (or variant if no As), before the next , or ).

nitt1995
06-26-2006, 10:53 AM
I presume that the code you have parses the module text looking for Sub and Function to get the names. Just extend the parsing to look after the (. The Type will be after As (or variant if no As), before the next , or ).

I am actually collecting the functions using the .ProcOfLine method. From what I can figure out, this returns the procedure name at a given line without parsing the code.

Function CollectProcedures(wb As Workbook, moduleName As String) As Collection
Dim VBCodeMod As CodeModule
Dim StartLine As Long
Dim TempData() As Variant
Dim ProcName As String

Dim i As Integer
Dim Count As Integer

Dim procedures As New Collection
Set VBCodeMod = wb.VBProject.VBComponents(moduleName).CodeModule
With VBCodeMod
'First Procedure is private, so don't include
StartLine = .CountOfDeclarationLines + 1
StartLine = StartLine _
+ .ProcCountLines(.ProcOfLine(StartLine, _
vbext_pk_Proc), vbext_pk_Proc)
Count = 0
Do Until StartLine >= .CountOfLines
Count = Count + 1
procedures.Add .ProcOfLine(StartLine, vbext_pk_Proc)
StartLine = StartLine + _
.ProcCountLines(.ProcOfLine(StartLine, _
vbext_pk_Proc), vbext_pk_Proc)
Loop
End With
Set CollectProcedures = procedures
End Function