Is there a Macros collection in Excel similar to the Names collection 3

[WantsToLearn]

If there a macros collection exposed in Excel that you can spin through to retrieve properties of a specific macro in VBA code?

Thanks!

 

[RobBroekhuis]

I'm pretty sure there isn't anything of the kind. What are you trying to accomplish?
Rob

 

[xlbo]

I would hestitate to jump in here as I do not know for sure BUT there must be some kind of internal collection for excel to be able to display the names of all macros in a workbook and therefore, I would assume that there is SOME way of accessing this
You can certainly go down to a module and count lines etc
(thisworkbook.vbcomponents("module1&quot.lines.count
but I havn't found the index for subs etc (yet)

As a last resort, the FIND function is available (manually at least) within a module. It may be that you can use this to search for "sub" and then take it from there... Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[xlbo]

Got it - posted by Hans Wittoeck on the excel L list:

Sub ListProcedures()
Dim VBCodeMod As CodeModule
Dim StartLine As Long
Dim msg As String
Dim ProcName As String
Dim i As Byte
With ThisWorkbook.VBProject
For i = 1 To .VBComponents.Count
msg = vbNullString
Set VBCodeMod = ThisWorkbook.VBProject.VBComponents(i).CodeModule
With VBCodeMod
StartLine = .CountOfDeclarationLines + 1
Do Until StartLine >= .CountOfLines
msg = msg & .ProcOfLine(StartLine, vbext_pk_Proc) & Chr(13)
StartLine = StartLine + .ProcCountLines(.ProcOfLine(StartLine, vbext_pk_Proc), vbext_pk_Proc)
Loop
End With
Debug.Print "Module: " & .VBComponents(i).Name, msg
Next i
End With
End Sub

This lists all modules and subs in the immediate window
Watch for word wrap

You will need to set the reference (in VBE) to Microsoft Viual Basic for Applications Extensibility Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[SkipVought]

Geoff,
CodeModule in not in my VBA library -- I get a compile error.

But I think that you must be right -- there's got to be a way to get that collection. Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[xlbo]

Skip - bizarre - works fine for me (97 SP2,NT)
Have you checked the MS VBA Extensibility reference in VBE>Tools>References ? Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[SkipVought]

Geoff,

Thanx! That is what I was missing.

GOOD POSTING! ==> * Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[WantsToLearn]

Thanks, all. User has several Excel files with multiple macros in each one. I am trying to build a macro control file that would only show the first one for each file to avoid the long cluttered list.

In other words, this file would display File1, Macro1, File2, Macro2, etc. When the user selected File1, my code opens File1 and runs Macro1 for her.

Thanks again for the help! I thought it might be buried in the project properties somewhere but it would have taken me a while to get there.