VBA - Identifying subs and functions called from other subs/procedures

Question

Struggling to come up with a title for this one. I have a project which contains around 20 forms / 10 code & class modules. This project has multiple functions which fit together nicely to solve specific purposes, but can also be segmented to satisfy similar needs in other projects. Those other projects may not necessarily need the other components.

An example of this may be: One part of the project can create a powerpoint, while another part of the project may be able to send emails. This project uses both, however another project may only need one of those functions.

I have tried my best to separate these functions into their self contained modules. However, I have placed functions that are commonly used in a separate module ("All") so that I am able to utilize the same code without repeating in various places. I am self-taught so if this isn't a best practice, let me know.

The issue is that I now want to be able to export certain modules from one project to the other. I am building a tool which will let me distribute these modules as fully functional packages that will work without any setup on the destination file. In order to accomplish this, I need to understand where my subs/functions are pointing. How interconnected are my modules? If I exported, lets say the PowerPoint module, does that module try and access something in my "All" module? If that's the case, I would need to also export any relevant code from that module to the new workbook.

If the first tier of procedures are the ones listed in the target module, then the second tier of procedures would be the ones called via the T1 procedures. This means that T3 procedures are called from T2 procedures and so on...

How would I write a Loop function that needs to go deeper and deeper? The exit condition is most certainly when all T1 procedures have been fully explored.

The below code simply gets me a list of procedures in the target module and adds them to an array.

Sub getProcs()

Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim VBMod As VBIDE.CodeModule
Dim lineNum As Integer
Dim lineText As String
Dim lineCount As Integer
Dim procArr As Variant
Dim procName As String
Dim ProcKind As VBIDE.vbext_ProcKind

'Set Target Objects
Set VBProj = ThisWorkbook.VBProject
Set VBComp = VBProj.VBComponents(2)
Set VBMod = VBComp.CodeModule
With VBMod
    If .CountOfLines > 0 Then
        lineNum = .CountOfDeclarationLines + 1
        Do Until lineNum >= .CountOfLines
            procName = .ProcOfLine(lineNum, ProcKind)
            procArr = all_dataArray(procArr, procName)
            lineNum = .ProcStartLine(procName, ProcKind) + _
                    .ProcCountLines(procName, ProcKind) + 1
        Loop
    End If
End With
End Sub

Function all_dataArray(arr As Variant, arrVal As Variant) As Variant
'Add item to array
If IsEmpty(arr) Or Not IsArray(arr) Then
    arr = Array(arrVal)
Else
    ReDim Preserve arr(0 To UBound(arr) + 1)
    arr(UBound(arr)) = arrVal
End If
all_dataArray = arr
End Function

Perhaps I am going about this all wrong, happy to hear any advice. Thanks for your time.

Answer 1

I have implemented it in the past but with no perfect results. It helped me to get an incomplete (or sometimes overloaded) list of dependencies and for the rest let the compiler complain to fix...

Also huge effort - rarely need it. In fact if you are doing good job in programming you try to split every function to small independed functions. So finally when you ask for dependecies of a single function, it turn outs that the answer is usuall almost your entire project!

This code is a whole module with references to custom general functions of other modules, for extracting token lists, concatanating arrays etc.

Any way I post some abstracts of functions I used. I don't know if could be helpfull.

Good luck!

1.

Function GetComponentProcDependences(Optional ComponentName As String = "Module1", Optional ProjectName As String = "")
[...]
objProject = VBAEditor.VBProjects.item(ProjectName)
[...]
PutativeDependences = Quicksort(ProjectFunctionList(objProject, False, False), True)
[...]
allreadyincluded = ComponentMethodsList(ReferencesComponent, False, False)
[...]
For Each item In allreadyincluded 
Call FindProcDependences(CStr(item), ProjectName, PutativeDependences, allreadyincluded)
Next
GetComponentProcDependences = allreadyincluded
End Function

2.

Function ProjectFunctionList(Optional objProject As VBIDE.VBProject = Nothing, Optional Titles As Boolean = True, Optional args As Boolean = True)
[...]
For Each objComponent In objProject.VBComponents
    cs = ComponentMethodsList(objComponent, Titles, args)
[...]
 ProjectFunctionList= ArrayConcatenate(ex, cs)
[...]
End Function

3.

    Public Function ComponentMethodsList(objComponent As VBIDE.VBComponent, Optional inclTitle As Boolean = True, Optional args As Boolean = True)
    [...]
     Dim objCode As VBIDE.CodeModule
    [...]
    Do While iLine < objCode.CountOfLines
    sProcName = objCode.ProcOfLine(iLine, pk)
    [...]
                    pclc = objCode.ProcCountLines(sProcName, pk)
                    pblc = objCode.ProcBodyLine(sProcName, pk)
    [...]
   pcl = Replace(objCode.Lines(pblc, decllines), " _", "") 'etc
    [...]
     Call AddToListSub(IIf(inclTitle, objComponent.Name & ".", "") & sProcName & 
    IIf(args, "#" & pcl, ""), res)
    [...]
    iLine = iLine + pclc
    [...]
    Loop
    ComponentMethodsList = res
    End Function

4.

Sub FindProcDependences(ProcName As String, ProjectName As String, PutativeDependences, Optional allreadyincluded = Nothing)


compcode = GetProcCode(ProcName, ProjectName) '
residue = Array("=", """", "-", "+", ";", "(", ")", "&", " ", ",", vbTab, vbCr, vbLf, ".")
compcode = MassReplace(compcode, "+", residue)
Do
i0 = Len(compcode)
compcode = Replace(compcode, "++", "+")
Loop While i0 <> Len(compcode)
tokens = tokenizer(compcode, False, "+", allreadyincluded)
Dim temparis()
For Each item In tokens
If ArrayContains(item, PutativeDependences, "bool") And Not ArrayContains(item, allreadyincluded) Then
Call AddToArrayListIfUnique(item, temparis)
End If
Next

[...]

allreadyincluded = ArrayConcatenate(allreadyincluded, temparis)

For Each item In temparis
Call FindProcDependences(CStr(item), ProjectName, PutativeDependences, allreadyincluded)  'Recursive calls
Next

End Sub

5.

Public Function GetProcCode(ProcName As String, Optional ProjectName As String = "")
Dim objComponent As VBIDE.VBComponent
Dim objCode As VBIDE.CodeModule
Set objComponent = FindComponentContainsProc(ProcName, ProjectName)
Set objCode = objComponent.CodeModule
iLine = objComponent.CodeModule.ProcStartLine(ProcName, vbext_pk_Proc)
pclc = objCode.ProcCountLines(ProcName, pk)
GetProcCode = objCode.Lines(iLine, pclc)
Set objComponent = Nothing
Set objCode = Nothing
End Function

6.

Function FindComponentContainsProc(ProcName As String, Optional ProjectName As String = "", Optional ProcKind As VBIDE.vbext_ProcKind = vbext_pk_Proc) As VBIDE.VBComponent
[...]
Dim objComponent As VBIDE.VBComponent
For Each objComponent In objProject.VBComponents
pupProcLine = 0
Dim objCode As VBIDE.CodeModule
Set objCode = objComponent.CodeModule
On Error Resume Next
pupProcLine = objCode.ProcStartLine(ProcName, ProcKind)
If pupProcLine > 0 Then Exit For
Next
[...]
End Function

Answer 2

Identifying the members/procedures of a module is only one step, and it's as far as the VBIDE API is going to be helpful.

The next step is to determine where the procedure calls are, and in order to successfully do this, you need to take the text of the module, and somehow determine what's a procedure call, and which procedure is being called: in order to reliably do this, you're going to need to "programmatically understand" the VBA code as well as VBA itself.

The only way to do this, is to turn the text into a stream of tokens (that's what a lexer does), turn that stream of tokens into a tree structure (that's what a parser does), then traverse these trees and populate a symbol table that contains information about what's declared in what scope and which scopes can "see" it. Then you need to traverse the parse trees again, locate the nodes representing procedure calls, find the name in your symbol table, and resolve the identifier reference to a specific symbol, in accordance with VBA's language scoping rules.

The tokenizing step needs to take into account pesky annoying things like comments, line continuations, and precompiler directives.

Once you've resolved identifier references, all you need to do is iterate the references to each procedure in your symbol table, and list their respective parent scope/procedure - then you know who's calling who.

I cannot think of any other reliable way to do this... and I can't imagine doing this in VBA. Rubberduck's parser is somewhere in the ballpark of 45,000 lines of C# code, generated by processing an Antlr4 grammar designed to VBA's language specifications. And that's not even the preprocessor and resolver parts, which have their very own challenges and complexities.

---

That said no, an "All" module isn't best practice. In fact it's a sure-shot way to get a dumping bag of whatever: if a module (or class) is a FooUtils, a FooManager, a FooHelper, or any other such vaguely-scoped loose term that basically stands for "whatever stuff I couldn't fit anywhere else", something is wrong with the design.

I don't know what your "commonly used" functions are, but it's likely that what you need is just further modularization. Extract these helper methods into more specialized modules/classes - a ListObjectExtensions module clearly states what's in scope for it, so you shouldn't expect to find e.g. a Get1DArrayFromRange function in there.