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?
Well-known indeed!
Hey, so after I set a reference to the Extensibility library, how do I actually point this thing at something?
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.
What’s the exact reference? Thanks ~ Eric
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.