… and other procedures.
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/WordPress/list-vba-procedures-by-vba-module-and-vb-procedure/
Jon had modified some code, that originally sent the list to a dialog, so that it pasted it into a new workbook instead. Nothing wrong with that, but it seemed to me more convenient to have it operate as a User Defined Function (UDF), returning an array, so that you could have your list wherever you wanted. A couple of other changes, while I was at it:
- I split the output into two columns, module name and procedure name.
- I started off creating a large array for the output, rather than re-diming for every new line.
Here’s the end result: (Or download GetProcedures.xlsb)
Function GetProcedures() '' 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# '' as modified by Jon Peltier '' http://peltiertech.com/WordPress/list-vba-procedures-by-vba-module-and-vb-procedure/ '' Modified by Doug Jenkins to operate as a UDF and return results as a 2 column array '' set a reference to the Microsoft Visual Basic for Applications Extensibility 5.3 Library ' Declare variables to access the Excel workbook. Dim app As Excel.Application Dim wb As Excel.Workbook 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 ' Add constant for output array size to avoid redims Const OutRows As Long = 10000 Set app = Excel.Application '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 OutRows, 1 To 2) sOutput(1, 1) = sFileName sOutput(2, 1) = 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, 1) = vbComp.Name sOutput(2 + iRow, 2) = 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, 1) sOutput(3, 1) = "Project protected" End If If iRow = 0 Then ' UBound(sOutput) = 2 Then ' ReDim Preserve sOutput(1 To 3) sOutput(3, 1) = "No code in project" End If sOutput(2, 2) = iRow GetProcedures = sOutput ' clean up Set vbProj = Nothing Next End Function
And here’s what the output looks like:
Doug … I get a debug with code … do you have a sample file … cheers, James
Link to sample file for download now added:
(It needs a reference to Microsoft VBA Extensibility 5 enabled, that may have been the problem).
Pingback: Daily Download 33: Miscellaneous | Newton Excel Bach, not (just) an Excel Blog