List VBA Procedures by VBA Module and VB Procedure

Mathias Brandewinder of Clear Lines Blog tweeted up Displaying a List of All VBA Procedures in an Excel 2007 Workbook from the Ribbon, an article by Microsoft’s Frank Rice on the MSDN web site, which showed a VBA procedure for listing all of the VBA procedures in a VB project. Interesting procedure, could be very useful.

Not being able to leave anything alone, I made some changes to how the procedure worked. The original put its list of procedures into a dialog, and when the dialog was closed, the list evaporated. I changed the output so that the list is dumped into a new worksheet inserted just for this purpose.

I broadened the scope of the procedure, so that it lists all procedures in all VB projects, not just the active one. The first two rows of the output sheet contain the names of the parent workbook and of the project.

If the project is protected, you’ll get an error if you try to look inside, so my code bypasses the error and notes that the project is protected. My code also notes when there isn’t any code in a project.

I also didn’t find the original variable names useful, so I used some which were more descriptive, to me at least.

Here’s my modified procedure, for what it’s worth.

'' Based on:
'' Displaying a List of All VBA Procedures in an Excel 2007 Workbook
''     from the Ribbon (June 2009)
'' by Frank Rice, Microsoft Corporation
'' http://msdn.microsoft.com/en-us/library/dd890502(office.11).aspx#

'' set a reference to the Microsoft Visual Basic for Applications Extensibility 5.3 Library

Sub GetProcedures()
  ' Declare variables to access the Excel workbook.
  Dim app As Excel.Application
  Dim wb As Excel.Workbook
  Dim wsOutput As Excel.Worksheet
  Dim sOutput() As String
  Dim sFileName As String

  ' Declare variables to access the macros in the workbook.
  Dim vbProj As VBIDE.VBProject
  Dim vbComp As VBIDE.VBComponent
  Dim vbMod As VBIDE.CodeModule

  ' Declare other miscellaneous variables.
  Dim iRow As Long
  Dim iCol As Long
  Dim iLine As Integer
  Dim sProcName As String
  Dim pk As vbext_ProcKind

  Set app = Excel.Application

  ' create new workbook for output
  Set wsOutput = app.Workbooks.Add.Worksheets(1)

  'For Each wb In app.Workbooks
  For Each vbProj In app.VBE.VBProjects

    ' Get the project details in the workbook.
    On Error Resume Next
    sFileName = vbProj.Filename
    If Err.Number <> 0 Then sFileName = "file not saved"
    On Error GoTo 0

    ' initialize output array
    ReDim sOutput(1 To 2)
    sOutput(1) = sFileName
    sOutput(2) = vbProj.Name
    iRow = 0

    ' check for protected project
    On Error Resume Next
    Set vbComp = vbProj.VBComponents(1)
    On Error GoTo 0

    If Not vbComp Is Nothing Then
      ' Iterate through each component in the project.
      For Each vbComp In vbProj.VBComponents

        ' Find the code module for the project.
        Set vbMod = vbComp.CodeModule

        ' Scan through the code module, looking for procedures.
        iLine = 1
        Do While iLine < vbMod.CountOfLines
          sProcName = vbMod.ProcOfLine(iLine, pk)
          If sProcName <> "" Then
            iRow = iRow + 1
            ReDim Preserve sOutput(1 To 2 + iRow)
            sOutput(2 + iRow) = vbComp.Name & ": " & sProcName
            iLine = iLine + vbMod.ProcCountLines(sProcName, pk)
          Else
            ' This line has no procedure, so go to the next line.
            iLine = iLine + 1
          End If
        Loop

        ' clean up
        Set vbMod = Nothing
        Set vbComp = Nothing

      Next
    Else
      ReDim Preserve sOutput(1 To 3)
      sOutput(3) = "Project protected"
    End If

    If UBound(sOutput) = 2 Then
      ReDim Preserve sOutput(1 To 3)
      sOutput(3) = "No code in project"
    End If

    ' define output location and dump output
    If Len(wsOutput.Range("A1").Value) = 0 Then
      iCol = 1
    Else
      iCol = wsOutput.Cells(1, wsOutput.Columns.Count).End(xlToLeft).Column + 1
    End If
    wsOutput.Cells(1, iCol).Resize(UBound(sOutput) + 1 - LBound(sOutput)).Value = _
        WorksheetFunction.Transpose(sOutput)

    ' clean up
    Set vbProj = Nothing
  Next

  ' clean up
  wsOutput.UsedRange.Columns.AutoFit
End Sub
 


Peltier Tech Chart Utility

Comments

  1. MZ tools has an option to spit out an xml file and has an associated style sheet to format it. Works great.

  2. Jan Karel –

    MZ Tools is a great utility. I use it all the time. And it has so many features that I am completely unaware of. Thanks for pointing out this reinvention of yet another wheel.

  3. This looks like it would work in Excel 2003 as well. But since you’re using early-bound references to the VBIDE library, you might want to mention that you need to set a reference to the Microsoft Visual Basic for Applications Extensibility (I assume 5.3).

    Also you have “On Error Resume Next” listed twice right before the start of the big If statement.

  4. Jimmy –

    Thanks, you can be my technical editor. The second On Error Resume Next was supposed to be On Error GoTo 0, and now it’s fixed. I also added a comment about the library reference.

  5. I haven’t had time to really dig into it, but at a glance, the variables are already much more pleasing to the eye! It’s funny how the urge to fix small things can be a powerful motivator. In any case, I am very happy that this code exists now: I was thinking of adding some difference tracking between the VBA code of 2 workbooks in Akin, but had never seen any code dealing with that part of the Excel object model – this example will go a long way for me. Now, my turn to get started and do something in .NET… Curious to see if I will have problems accessing the VBA object model from there!

  6. Hi Mathias –

    I was just thinking I should email you with the link to this article, but I see you’ve found it.

    A VBA difference tracker will make a nice addition to your Akin utility, more useful to me I think than a worksheet difference tracker. I think as long as you set your references properly, you should have no (or minimal) problems accessing the code.

  7. Saw your post and followed you here, is there a C# library or this packaged so that I can turn the variables and sections in a worksheet into a class.

  8. Clooge –

    I don’t do any C# (except on the guitar, mostly by accident), so I can’t guide you there. I also don’t know how the functionality in a worksheet can be encoded in VBA or C#, other than by hand.

    I wish I remembered that I had written this article. I needed just this functionality the other day, and ended up doing it by hand.

  9. Hi Jon,
    I need to find the code bearing needles in a haystack of close to a million (yes, 1,000,000) Excel files, so opening each file is not really an option.

    Would you have any idea how to check a large number of files for code included?

  10. Larry Seidenberg-Jones says:

    How can this be modified to get subs and functions from MSAccess 2003 forms and modules?

  11. Larry –

    I don’t know Access well enough to answer. I don’t even have it installed anymore.

  12. Larry Seidenberg-Jones says:

    Thanks for the reply anyway Jon. Have a great day.

    Larry

  13. Is there a way to know, whether the procedure is a “Sub” or a “Function”?

  14. It’s easy to tell whether the procedure is a Property Let, Property Set, or Property Get procedure, since each has a unique identifier. Unfortunately the fourth identifier is shared by subs and functions.
    You could parse the code to look for “Sub” or “Function”. But since blank and comment lines above the Sub or Function line are considered part of the procedure, you may have to check a number of lines of code before you find the string you need.
    And of course, if someone has used “Sub” or “Function” within one of the comment lines, you could find the string and not find the proper procedure identifier. So you first need to know whether a line is the first line of a comment (the first non-space character is a single quote) or a continuation of the previous comment line (if the previous line is a comment line and ends in the underscore character).
    I guess what I would do is stick this code into a new function and call it from within the loop that finds the name of the procedure.
    I also think what I would do instead of just get the name of the procedure and whether it’s a sub or function, is extract the first line (or lines if extended by the underscore) of the procedure, so I could examine what arguments the procedure calls and whether by reference or by value, and if it’s a function, what variable type is returned.

  15. I too am curious about reading/parsing the code for content as well as running against Access procedures and macros. I will try the Access part first since my immediate goal is a function to document a database and produce a listing in Excel.

Subscribe without commenting

Trackbacks

  1. [...] most commonly seen, and the idea of VBA code interacting with VBA code is fun. So I tweeted it, and Charts GrandMaster Jon Peltier, in his own words,  could not “leave anything alone, and made some changes to how the [...]

  2. Read Excel VBA macros and functions through C#…

    Read Excel VBA macros and functions through C#…

  3. [...] I wanted to make a list of all the VBA procedures in a workbook, and discovered that Excel doesn’t have a built-in way of doing that.  People have written code to work their way around that of course, and the code I ended up using came from Jon Peltier’s site: http://peltiertech.com/list-vba-procedures-by-vba-module-and-vb-procedure/ [...]

Write a Comment

I welcome comments from my readers. If you have an opinion on this post, if you have a question, or if there is anything to add, I want to hear from you. Whether you agree or disagree, please join the discussion. If you want to include an image in your comment, post it on your own site or on one of the many free image sharing sites, and include a link in your comment. I'll download your image and insert the necessary html to display the image inline.

Subscribe without commenting

Peltier Tech Chart Utility

 

Create Excel dashboards quickly with Plug-N-Play reports.