Disappearing Macro Names

[Cyberdude]

I?ve never understood why the names of non-function macros (i.e., ?Subs?) that have at least one argument defined never appear on the macro name list (index). I solved that inconvenience for myself years ago by preceding every macro that has an argument with a two or three line ?Sub? macro that has the same name with ?_Locator? appended to the end of the name. That way, when a macro name disappears, I can look for the same name followed by ?_Locator? in the macro name list. I know, it?s clutzy, but it works.

So why do ?arged? Subs lose the privilege of being listed with the other macro names?

[geekgirlau]

Because you can't run it without providing the parameters, which means that it must be called from within another macro or function. Excel doesn't allow you to type in the arguments on the fly (in the way that a parameter query works in Access). Because you can't run it directly, I gues they figured there was no point displaying it in the list.

[Cyberdude]

Hey, geekgirlau, thanx for the reply. Are you guessing about that or is that actually the reason? It seems to me that the people who use the macro name list are mostly folks who are writing other macros, at least it seems that way. So it would also seem that such a rule would affect them the most. Not nice ... not nice at all. How do others deal with this nuisance.? I get the feeling that I'm the ONLY one who uses the "_Locator" appendage technique, and I gotta say, I'm not proud of it.

[johnske]

Although macro and procedure are often used interchangeably, in very broad terms a "Procedure" is generally used to describe a stand-alone segment of VBA code that you have written. Where-as a "Macro" would more aptly describe a task or procedure that's been created solely with the Macro recorder and is left unaltered.

However, to be very specific, a "Procedure" in the broader term applies to any unit of code enclosed either between the Sub and End Sub statements or between the Function and End Function statements while Macro is a specific term that applies only to public Sub procedures.

All macros are procedures, but not all procedures are macros. However the defining difference between a Macro and a Procedure given by Microsoft is really that of 'Scope', only public procedures (i.e. only procedures you generate with the macro recorder and all procedures that can be run from the Macros dialog box) are defined as Macros.

By this last definition, no private procedures can be defined as macros and listed in the macros dialog box (as you cannot use the macro recorder to generate a procedure requiring arguments {or a function} these are not macros, they're really private procedures by default).

i.e. if it's not listed in the macros dialog box it's because it's not a macro

[johnske]

BTW, to list the names of all procedures in your open workbook, you could keep this in your PERSONAL.XLS...

Option Explicit
'Sub ListProcedures()
On Error Resume Next '< error = reference already set
'set reference to Microsoft Visual Basic for Applications
'Extensibility 5.3
ThisWorkbook.VBProject.References.AddFromGuid _
"{0002E157-0000-0000-C000-000000000046}", 5, 3
'now get the list of procedures
Call GetTheList
'End Sub

Private Sub GetTheList()
Dim N&, Count&, MyList(200), List$
Dim Component As VBComponent
For Each Component In ActiveWorkbook.VBProject.VBComponents
With Component.CodeModule
Count = .CountOfDeclarationLines + 1
Do Until Count >= .CountOfLines
MyList(N) = .ProcOfLine(Count, _
vbext_pk_Proc)
Count = Count + .ProcCountLines _
(.ProcOfLine(Count, vbext_pk_Proc), _
vbext_pk_Proc)
List = List & vbCr & MyList(N)
If Count < .CountOfLines Then N = N + 1
Loop
End With
N = N + 1
Next
MsgBox List, , "Procedure List"
End Sub

[geekgirlau]

Trust John to have not only the technical explanation (as opposed to my 25-words-or-less version ) but some nice code to work around the issue!

[johnske]

Trust John to have not only the technical explanation (as opposed to my 25-words-or-less version ) but some nice code to work around the issue!

Well I thort your short answer was pretty good, but from past experience I guessed that with Sid's enquiring mind there'd be a lot more questions to follow, so I thort I'd cover mosta the bases straight off. (the code's in the KB as ListAllMacros {I think} as 'macros' is what most ppl refer to procedures as, and would search the KB for)

[Cyberdude]

I'm overwhelmed! You guys sure delivered the goods. Actually, John, I wrote a "macro" to list all my "procedures",
and it does a bangup job of presentation. But I never successfully (without problems) was able to click on a name and have it bring up the macro/procedure in the VBE. I like the macro lister that is available, but it has this one quirk of not including names that have args. Despite your fascinating and educational rhetoric, John, it doesn't really explain the logic of having such discrimination. But I can live without it. Thanks to both of you for the enlightenment.