How to get a list of *runnable* macros

[jasmith4]

In Excel's UI you can hit Alt-F8, and in its VBIDE you can hit F5 -- both times you'll get a list of macros. But the only macros listed (as best I know):

are Subs, not Functions
have either no parameters or all Optional Variant-type parameters
are in the workbook object, a worksheet or chart object or in a regular module that has no Option Private Module statement


I know how to pull in VBA Extensibility and then iterate through all procedures in only objects and modules (not class modules) to get all procedures' names, but how do I check for all the above rules that make a macro legal to run?

[mancubus]

this may help you:
https://msdn.microsoft.com/en-us/library/dd890502(v=office.11).aspx

[jasmith4]

Unfortunately this is the exact opposite of what I'm looking for. This shows how to display *all* procedures, and I can do that. I want something that displays runnable macros *only*. Or, I want VBA code that can determine "runnable" -- no parameters, Subs rather than Functions, no Option Private Module, etc. -- preferably without having to parse lines of code!

[Kenneth Hobs]

We have no way of knowing your expertise level. The link you were given can be of help to others that don't know how to get where you are at.

The task would be very involved. Chip provides some code tips that you can try. While you won't get all that you need, once you know how to get say the first line of a routine, if the word Private is in it, or Function, then those can be eliminated.

http://www.cpearson.com/excel/vbe.aspx

[jasmith4]

Kenneth, that link is very helpful -- in fact I've conversed with Mr. Pearson (www.cpearson.com (http://www.cpearson.com)) before about other Excel issues, and he's great, as is Andy Pope (www.andypope.com (http://www.andypope.com)). What Pearson's code does is what I figured I'd have to do all along: parse lines of code.

Pearson's code includes a way to return information about any procedure, including its scope: public, private, friend or default, but his code doesn't look for "Option Private" or "Option Private Module" to determine what the default is. Now, that would be easy, even taking comments into account.

But here are some hard parts:

If a regular Module has "Option Private Module", even a Public Sub with no parameters does not appear.
A Public Sub must have no parameters or all Optional Variant parameters. If any parameter, even with a default value, is not a Variant, the Sub doesn't show up.


...and that last one is pretty hard to parse...

[Kenneth Hobs]

Yes, that is why I said it is involved.

While a bit annoying, one could open the dialog and use API routines to get the contents of the listbox that Excel fills for you. OF course if you can even do that, you will need API spy tool to see what window handles and classes you need to deal with. I have not used one to see if we can do it but if the idea appeals to you, let me know.

[Paul_Hossler]

but how do I check for all the above rules that make a macro legal to run?


Just curious but why do you want a list like that?

[jonh]

Nothing to add, but curious what the point would be in being able to list 'macros'.
Unless it's some kind of documtation; how would you run the code anyway?

Procedures are generally "runable" otherwise there wouldn't be much point in them.
A function is just a sub that returns a value so not sure why you'd want to exclude them...?

Edit
what PH just said