Listing Calling Procedures

By in Visual Basic Editor on .

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. Jeff Weir

    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.

  2. Dick Kusleika Post author

    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. http://www.cpearson.com/excel/vbemenus.aspx I should probably do a post on that, but it’s mostly just stolen from Chip’s site.

Leave a Reply

Your email address will not be published. Required fields are marked *

To create code blocks or other preformatted text, indent by four spaces:

    This will be displayed in a monospaced font. The first four 
    spaces will be stripped off, but all other whitespace
    will be preserved.
    
    Markdown is turned off in code blocks:
     [This is not a link](http://example.com)

To create not a block, but an inline code span, use backticks:

Here is some inline `code`.

For more help see http://daringfireball.net/projects/markdown/syntax