Results 1 to 4 of 4

Adding Parameter Description to VBA module list

Threaded View

popfan Adding Parameter Description... 07-19-2012, 04:05 PM
Andy Pope Re: Adding Parameter... 07-20-2012, 05:04 AM
popfan Re: Adding Parameter... 07-20-2012, 10:20 AM
Andy Pope Re: Adding Parameter... 07-20-2012, 10:33 AM
  1. #1
    Registered User
    Join Date
    07-20-2011
    Location
    Missouri, USA
    MS-Off Ver
    Excel 2007
    Posts
    36

    Question Adding Parameter Description to VBA module list

    I am using the code from Microsoft to pull my procedure name in VBA, but I would like to add the function to pull back the parameters also. I cannot figure out what need to be added to it.
    http://msdn.microsoft.com/en-us/libr...ice.11%29.aspx

        ' Declare variables to access the Excel 2007 workbook.
        Dim objXLApp As Excel.Application
        Dim objXLWorkbooks As Excel.Workbooks
        Dim objXLABC As Excel.Workbook
        
        ' Declare variables to access the macros in the workbook.
        Dim VBAEditor As VBIDE.VBE
        Dim objProject As VBIDE.VBProject
        Dim objComponent As VBIDE.VBComponent
        Dim objCode As VBIDE.CodeModule
        
        ' Declare other miscellaneous variables.
        Dim iLine As Integer
        Dim sProcName As String
        Dim pk As vbext_ProcKind
        
        Set VBAEditor = Application.VBE
        
        ' Open Excel and the open the workbook.
        Set objXLApp = New Excel.Application
        
        ' Empty the list box.
        ListBox1.Clear
        
        ' Get the project details in the workbook.
        Set objProject = VBAEditor.ActiveVBProject
    
        ' Iterate through each component in the project.
        For Each objComponent In objProject.VBComponents
    
            ' Find the code module for the project.
            Set objCode = objComponent.CodeModule
    
            ' Scan through the code module, looking for procedures.
            iLine = 1
            Do While iLine < objCode.CountOfLines
                sProcName = objCode.ProcOfLine(iLine, pk)
                If sProcName <> "" Then
                    ' Found a procedure. Display its details, and then skip
                    ' to the end of the procedure.
                    ListBox1.AddItem objComponent.Name & ": " & sProcName
                    iLine = iLine + objCode.ProcCountLines(sProcName, pk)
                Else
                    ' This line has no procedure, so go to the next line.
                    iLine = iLine + 1
                End If
            Loop
            Set objCode = Nothing
            Set objComponent = Nothing
        Next
        
        ' Clean up and exit.
        Set objProject = Nothing
        objXLApp.Quit
    Sample output:
    DATAWHSE: openconnection2
    DATAWHSE: GetUser
    DATAWHSE: GetAnalyst
    DATAWHSE: GetDemand
    DATAWHSE: GetQOH
    DATAWHSE: GetOfm

    Sample of Module Code:
    Function GetDemand(Line, Item) As Double

    What I would like to see outputted:
    DATAWHSE: GetDemand ;Line, Item


    Any and all assistance is appreciated.
    Last edited by popfan; 07-20-2012 at 11:01 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1