Listing Calling Procedures

I have this awesome machine with 64-bit Office sitting under my desk. I don’t use it to code because MZ-Tools doesn’t work on 64-bit Office and I need that (and a few other things) to be productive. I only use a few features from MZ-Tools, so I think I’ll just write them in VBA. I took my first stab at the Procedure Callers feature.

Public Sub ListProcedureCallers()
    Dim vbProj As VBProject
    Dim vbModule As VBIDE.CodeModule
    Dim vbComp As VBIDE.VBComponent
    Dim i As Long
    Dim lActiveLine As Long
    Dim sProc As String
    'get the name of the current procedure
    Application.VBE.ActiveCodePane.GetSelection lActiveLine, 0, 0, 0
    sProc = Application.VBE.ActiveCodePane.CodeModule.ProcOfLine(lActiveLine, vbext_pk_Proc)
    'only look in the active project
    Set vbProj = Application.VBE.ActiveVBProject
    'loop through the code modules
    For Each vbComp In vbProj.VBComponents
        Set vbModule = vbComp.CodeModule
        'print the procedure for any line that contains the name of the active procedure
        If vbModule.CountOfLines > 0 Then
            For i = vbModule.CountOfDeclarationLines To vbModule.CountOfLines
                If InStr(1, vbModule.Lines(i, 1), sProc) > 0 And vbModule.ProcOfLine(i, vbext_pk_Proc) <> sProc Then
                    Debug.Print vbComp.Name, vbModule.ProcOfLine(i, vbext_pk_Proc), vbModule.Lines(i, 1), i
                End If
            Next i
        End If
    Next vbComp
End Sub

I just wanted to get something down and not be too worried about how well it works. This procedure just prints to the Immediate Window rather than a fancy userform that let’s you go directly to one of the procedures.

One of the things I don’t like about MZ-Tools is that it searches for callers in all open projects. I can see that value in that, I just personally have never needed it. And for procedures with common names, it shows a crap ton of stuff. I made my procedure only search the current project.

One of my property procedures in one of my class modules is named Active. When I looked for its callers, I got every procedure that uses ActiveWorkbook or ActiveSheet. My code does not discriminate – if the name of the procedure appears in the line of code, it’s a hit.

How do I avoid that? For the Active property, all I have to do is look for a space after the word Active and I should be good to go. Except for comments, perhaps. That’s fine for a property with no arguments, but if it has arguments or is a method with arguments, there won’t be a space after it but a parenthesis. Can I search for either a space or a paren? Seems like it, but I’ll have to think it through.

Another thing I don’t like about MZ-Tools is that it doesn’t care what class module you’re in when you look for calling procedures. Every one of my Collection Classes has an Add method. When I search for procedure callers for Add, I get every call to every Add method in every class.

That’s a little tougher proposition. I could be very opinionated, as I am, by looking for clsPlural.Add rather than just Add. I always name my class instance variables clsXXX. That would work for me, but wouldn’t be very general purpose. While I’m a well-known selfish prick, I do still care about you, dear reader. Even if I were so inclined, I’d have to still look for With blocks. I can’t just look for clsPlural.Add, I have to also look for .Add, then I have to search up the lines of code for a With before I hit an End With, then I have to determine the variable… My goodness that sounds like a lot of work. This is probably why MZ-Tools doesn’t care which Add method I’m looking for – it’s just not worth it.

Here’s some things I’d like to do:

  • Find actual callers, not just the procedure name
  • Omit finds in comments
  • When I’m in a class, only find properties/methods from that class
  • When I’m on a Property Get, don’t return Property Let assignment statements
  • Go to the first caller automatically, but still list the rest somewhere
  • Other stuff I haven’t thought of

What say you?


  1. Jeff Weir says:

    Well-known indeed!

    Hey, so after I set a reference to the Extensibility library, how do I actually point this thing at something?

  2. Jeff Weir says:

    Ah…via Tools=>Macros. Or by whatever shortcut key combo launches the Macro Dialog box in the VBE. I know it’s Alt F8 in Excel, but not in the VBE.

  3. Eric says:

    What’s the exact reference? Thanks ~ Eric

  4. Dick Kusleika says:

    The reference is Microsoft Visual Basic for Applications Extensibility (the one above the selected one)

    To kick it off, I added a menu item under Tools – DKTools using this technique. I should probably do a post on that, but it’s mostly just stolen from Chip’s site.

Posting code or formulas in your comment? Use [cc] tags!

  • [cc_vb]Block of code goes here[/cc_vb]
  • [cci_vb]Inline code goes here[/cci_vb]
  • [cc]Formula goes here[/cc]

Leave a Reply