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
Jan Karel Pieterse says
MZ tools has an option to spit out an xml file and has an associated style sheet to format it. Works great.
Jon Peltier says
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.
JP says
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.
Jon Peltier says
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.
Mathias says
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!
Jon Peltier says
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.
clooge says
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.
Jon Peltier says
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.
Daniel says
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?
Larry Seidenberg-Jones says
How can this be modified to get subs and functions from MSAccess 2003 forms and modules?
Jon Peltier says
Larry –
I don’t know Access well enough to answer. I don’t even have it installed anymore.
Larry Seidenberg-Jones says
Thanks for the reply anyway Jon. Have a great day.
Larry
Sanjay Rath says
Is there a way to know, whether the procedure is a “Sub” or a “Function”?
Jon Peltier says
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.
RK Greene says
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.
Steve says
Fyi, this works beautifully in Excel 2016/Office 365.
Quick usage instructions:
1. Ctrl+F11 to open the editor
2. copy this VBA code into ThisWorkbook
3. Tools > References > select “Microsoft Visual Basic For Applications Extensibility 5.3”
3. call the function (Ctrl+G to open the Immediate Window, type “call GetProcedures” and press Enter).
Results will output in a new worksheet.
Thanks, Jon!