Get Args needed for UDF

[Roderick_E]

I can generate a list of UDF with the following macro, but I'd like to put the args needed next to them. You can manually get a UDF args by pressing CTRL+SHIFT+A so there must be a way to do it programmically. Anyone?

Sub ListProcedures()
Dim sProc() As String
Dim lngLine As Long
Dim VBCodeMod As VBComponent
Dim sLine As String, sProcName As String, s As String
Dim vType As Variant
Dim AllWorkbooks As New Collection


For Each adn In AddIns
    If adn.Installed Then AllWorkbooks.Add (adn.Name)
Next


For Each wbk In Workbooks
    AllWorkbooks.Add wbk.Name
Next


ReDim sProc(1 To 2, 1 To 1)


r = 1
vType = Array("Function", "Public Function", "Private Function")


For Each wbk In AllWorkbooks
    If Workbooks(wbk).VBProject.Protection = vbext_pp_none Then
        For iType = LBound(vType) To UBound(vType)
            For Each VBCodeMod In Workbooks(wbk).VBProject.VBComponents
                With VBCodeMod.CodeModule
                    lngLine = .CountOfDeclarationLines + 1
                    Do Until lngLine >= .CountOfLines
                    
                        sLine = .Lines(lngLine, 1)
                        
                        If Left(sLine, Len(vType(iType))) = vType(iType) Then
                            ReDim Preserve sProc(1 To 2, 1 To r)
                            sProc(1, r) = Right(sLine, Len(sLine) - Len(vType(iType)) - 1)
                            sProc(2, r) = wbk
                            r = r + 1
                        End If
                        
                        lngLine = lngLine + 1
                        
                    Loop
                End With
            Next VBCodeMod
        Next iType
    End If
Next wbk


For n = 1 To r - 1
    sLine = sProc(1, n)
    sProcName = Left(sLine, WorksheetFunction.Find("(", sLine) - 1)
    s = s & "=" & sProcName &  vbCrLf [SIZE=4][COLOR=#ff0000][B]'would like to put ARGS here[/B][/COLOR][/SIZE]
Next n


MsgBox s
End Sub

 

[Roderick_E]

Figured it out change to this line:

sProcName = Left(sLine, InStr(sLine, ")"))