PDA

View Full Version : Sleeper: Find all macros used



mayur_m3
03-21-2005, 06:06 AM
Hi

Is there any trick how can I find all the macros used in a particular "sheet" not the whole workbook but a particular sheet

Regards,
mayur

johnske
03-21-2005, 06:22 AM
Hi Mayur,

Just go into the VBE window and double-click the sheet module you want...

HTH
John

mayur_m3
03-21-2005, 10:09 PM
Hi

The worksheet has no buttons or combo boxes
The worksheet uses some macros from the personal.xls which is in place
Which all macros are used?
How do U identify this?
Something similar to the formulas we find "=" in the whole sheet
What for the macros?

regards,
mayur

Cyberdude
03-22-2005, 10:11 AM
The pnly thing that comes to mind (and it's clumsy) is to define a static variable (count accumulator) in Personal for each macro that might be used. As each macro is entered, it can ask what is the active sheet. If it's the one of interest, then it could add 1 to the counter. I told you it's clumsy.

MWE
03-22-2005, 07:58 PM
Hi

The worksheet has no buttons or combo boxes
The worksheet uses some macros from the personal.xls which is in place
Which all macros are used?
How do U identify this?
Something similar to the formulas we find "=" in the whole sheet
What for the macros?

regards,
mayur
I am confident that "somewhere" there is a documented and fully coded method to do this. But since I do not know where that is:banghead:, a few thoughts:

It would be possible to interrogate the code modules in the open workbook and all referenced libs (for which you have source) and build "maps" for any given "parent" procedure. Thus if you knew that a given workbook would use, say, ProcA, ProcB and ProcC, you could, via one of these maps, understand all child procs down to the dll level. I can envision a recusive routine that would work its way from any given "top" down to the "bottom".
A (partial solution) alternative would have been to use good coding conventions and clearly document each child proc used by a given parent. I comment each proc I write and include, for example:

' Public/Private Variables used: NONE
' VB/VBA procedures called:
' mid
' len
' MATools/MWETools procedures called:
' MWE_InRangeI
' External Files Accessed: NONE

This would make it much easier to figure out maps but would also mean that the map is completely dependent on the developer's comments vs the actual code.:dunno