Track all supporting sub/fx

Dr. Demento

Well-known Member
Joined
Nov 2, 2010
Messages
618
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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.
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
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?
 
Upvote 0
Unfortunately, add-ins are not allowed either. Sorry, forgot to mention.
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,915
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top