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.

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