Results 1 to 6 of 6

Want list of Macros

This is a discussion on Want list of Macros within the Excel Questions forums, part of the Question Forums category; Hello, I want a list of Macros associated with an Excel Workbook by creating an object of Excel in VB ...

  1. #1
    New Member
    Join Date
    May 2002
    Location
    Pune, India
    Posts
    2

    Default

    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.

  2. #2
    Board Regular
    Join Date
    Apr 2002
    Location
    Cape Town,South Africa
    Posts
    234

    Default

    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


  3. #3
    MrExcel MVP Joe Was's Avatar
    Join Date
    Feb 2002
    Location
    Central Florida, USA
    Posts
    7,539

    Default

    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

  4. #4
    New Member
    Join Date
    May 2002
    Location
    Pune, India
    Posts
    2

    Default

    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 !!!

  5. #5
    MrExcel MVP Joe Was's Avatar
    Join Date
    Feb 2002
    Location
    Central Florida, USA
    Posts
    7,539

    Default

    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

  6. #6
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209

    Default

    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



    Kind Regards,
    Ivan F Moala From the City of Sails

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com