Track all supporting sub/fx

[Dr. Demento]

Recently, my group policies shut me out of using personal.xlsb. I have a work-around of copying all pertinent code from personal.xlsb into a new macro-enabled workbook and run it from there. Unfortunately, this is very time consuming to find all the necessary subs and functions to run a macro AND I have to do this each time I have a new project or each time Excel is shut down.

My current solution is to export the necessary modules into a folder as I step thru, but I'm looking for a slicker and more comprehensive way to do this. On issue is when I copy the entire module, I wind up copying extraneous functions/subs that VBA then makes sure I have all the supporting subs as well.

What I'm hoping to do is create a sub that goes thru each sub and function, line by line, capturing the name of the parent sub/fx, and then all subsequent calls to other child/supporting subs/fx, as many layers as there are. I understand there's no native way to do this.

My initial thoughts are to:

1. Compile a list of all subs and functions within personal.xlsb (done, thanks to Chip Pearson's excellent code).
2. Loop thru each module / each line of code to identify the name of the any matches against the comprehensive list of sub/fx
3. Capture module.sub/fx name into a dictionary

Alternatively, I could print the sub/fx name for each line as the code executes and then clean it up afterwards (but how to make a function fire on each line of code run, I have no clue).

Does this approach seem feasible? I realize I'm missing some steps but I'm kind of scrambled at the moment. I'm looking at over 3000 sub/fx, so updating each one is probably not in the cards.

Thoughts or suggestions??

Thanks y'all.

 

[Z51]

Does the policy prevent you from using all add-ins, or just personal.xlsb? If add-ins are allowed, couldn't you save a workbook containing your code as an .xlam file and loading that as an add-in for all new workbooks?

 

[Dr. Demento]

Unfortunately, add-ins are not allowed either. Sorry, forgot to mention.