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?

4 thoughts on “Listing Calling Procedures

  1. Well-known indeed!

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

  2. 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. 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? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.