VB Editor

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

When working in the Visual Basic for Applications Integrated Development Environment (VBIDE, or “VB Editor” for short), you can choose a number of settings to make your programming experience better. I’ll show the settings I use along with my rationale for choosing them.

First, go to the IDE’s Tools menu, and choose Options, to bring up this dialog. 

Two of these settings are must do‘s: Require Variable Declaration should be checked and Auto Syntax Check should be unchecked.

Require Variable Declaration automatically inserts the line “Option Explicit” at the top of each new module created in your projects. This line ensures that all variables are declared prior to being used in a procedure. This forces you to declare variables, and therefore to give some thought to how they are declared and used, and it prevents the use of undeclared variables.

One practical benefit of this behavior is that it prevents use of misspelled variable names. In the function below, the variable bTest is assigned a value of true, and presumably the returned value of MyFunction is supposed to be the value of bTest. However, in the command that assigns the value of MyFunction, the variable is misspelled as bTset. Since this variable has not been declared or used yet, MyFunction picks up the default value of a Boolean variable, which is False. You might spend hours checking the algorithm of your functions without realizing you have a misspelled variable name. It can be even harder to find if the variables are named bTest1 and bTestl (numeral one and lower case ell).

Since the Option Explicit declaration requires variables to be declared before being used, it will not allow the variable bTset to be used. When the function is compiled or run, the undeclared variable will be highlighted and a message will explain why: “Variable not defined”.

Auto Syntax Check pops up an error message any time there is a compile error in a newly entered line in a code module. In the example below, the line “If bTest” was typed, and the Enter key was pressed. Since this constitutes a compile error (If without Then), the erroneous line was highlighted red, and a message box is displayed.

This message box becomes tedious very quickly. The text is highlighted red, and remains red until the syntax is corrected, so you can tell there’s an error. The message box is redundant. Unchecking Auto Syntax Check removes this annoying message box, but the red highlighting of erroneous code is retained.

The rest of the settings I describe are my own preferences, but here they are for what it’s worth. The dialog at the top of this post shows a couple settings I use. First, I use a custom tab of 2 spaces instead of 4. This keeps highly indented code from extending too far to the right, so I can read it in a module without horizontal scrolling, and so I can print it out without line wrapping.

Second, I suppress the display of a procedure separator. Probably few people uncheck this setting, but the routine I use to print out code extracts the text to Microsoft Word, where it is formatted and given informative headers and footers in preparation for printing (maybe sometime I’ll publish this utility). The separator doesn’t make the export into Word, so I’ve gotten into the habit of using simple text lines as separators, which are included in the Word document. I use an apostrophe with a line of equal signs as a major separator, and an apostrophe with a line of dashes as a minor separator. The minor separator might separate related procedures, while the major separator denotes unrelated procedures.

The other options I typically set relate to docking of VBAIDE windows.

I uncheck docking for the Immediate Window and the Locals Window. If I used the Watch Window much I’d probably uncheck it as well. For me, the whole VB window isn’t large enough to have lots of windows docking along its edges and infringing on my module space. I’d rather switch between windows to access these particular windows. I also never maximize a module window within the IDE, because I like knowing what ther windows are open. This is different from my normal behavior in an application, which is to keep windows maximized unless I’m specifically comparing the contents of different documents.

I’m sure everyone has their own preferences and ways of interacting with their programming environment. Aside from the first two settings, everything else here is my own opinion and my own good or bad habits.