Want list of Macros

[murtuza]

Hello,

I want a list of Macros associated with an Excel Workbook by creating an object of Excel in VB.

Can anyone please help.

-Murtuza.

 

[gareth]

Good One.

Maybe some of the others can also use this one

Dave Hawley can correct me if I am wrong,but they have a brilliant website with a bunch of neat VBA's

www.ozgrid.com
Not 100% sure of the address,but there it is.

=========
Have fun with it

 

[Joe Was]

This will only display the number of projects, workbook macro work areas, in your workbook. Hope its gets you started. JSW

Sub myMac()
'Run from module.
'Will display the number of macro-work-areas(Projects in workbook).
MsgBox "Workbook contains [" & Application.VBE.ActiveVBProject.Collection.Count _
& "] Macro work areas!"

End Sub

 

[murtuza]

Well Joe, this code is something different. This code does not even returns the number of Macros present. But even I get number of macros correct, its not going to solve my problem, I need the names.

I have searched a lot on net but no success. I never knew I have asked such a tricky question !!!

 

[Joe Was]

The code does not return the number of macros it gets all the macro work areas as a count, so you know how many areas will need to be searched for your macros! JSW

 

[Ivan F Moala]

On 2002-05-17 00:54, murtuza wrote:
Hello,

I want a list of Macros associated with an Excel Workbook by creating an object of Excel in VB.

Can anyone please help.

-Murtuza.

Not tested But;

This example may help you out.
I gather you have the references set up
in your VB Project.

<pre/>
Sub Commandbutton()

Dim xlApp As Object
Dim VBCodeMod As CodeModule
Dim StartLine As Long
Dim ProcName As String
Dim VBComp
Set xlApp = GetObject("C:ExcelFilesUseful###.xls")

' Make it visible...
'xlApp.Visible = True

'// Set references
Dim xlModule As Object
Set xlModule = xlApp.VBProject.VBComponents

For Each VBComp In xlModule
Set VBCodeMod = xlApp.VBProject.VBComponents(VBComp.Name).CodeModule
If VBComp.Type = 1 Or VBComp.Type = 100 Then
With VBCodeMod
StartLine = .CountOfDeclarationLines + 1
Do Until StartLine >= .CountOfLines
MsgBox .ProcOfLine(StartLine, vbext_pk_Proc)
StartLine = StartLine + .ProcCountLines(.ProcOfLine(StartLine, _
vbext_pk_Proc), vbext_pk_Proc)
Loop
End With
End If
Set VBCodeMod = Nothing
Next VBComp

' Remember to release module
Set xlModule = Nothing
Set xlApp = Nothing
' Clean up

End Sub
</pre>