VSTO C# list names of macro in Excel

Posted on 2009-04-23
Last Modified: 2013-12-17
Using C# in a VSTO project. Can't seam to figure out how to list the names of the macros so user can select form them in a list box.

 I thought this would get me close but can't get it figured out
Question by:mhopkins9901
    LVL 23

    Accepted Solution

    You could try the converting the following code (sorry, but my C# skills are not all that good).  The strMacroList() array gets populated with the macro names which you could set your list to.  Or, you could modify the code to just add each entry to your list as it is found.
    Hope that helps!

    Private Sub GetMacroList()
    Dim strMacroList() As String
    Dim c As Long
    Dim n As Long
    With Application.ThisWorkbook.VBProject.VBComponents("Module1").CodeModule
    'would assume you would use
    'With this.Application.ThisWorkbook.VBProject.VBComponents("Module1").CodeModule
        c = .CountOfDeclarationLines + 1
        Do Until c >= .CountOfLines
            ReDim Preserve strMacroList(n)
            strMacroList(n) = .ProcOfLine(c, 0)
            c = c + .ProcCountLines(.ProcOfLine(c, 0), 0)
            If c < .CountOfLines Then n = n + 1
    End With
    End Sub

    Open in new window


    Author Comment

    Thanks for the help irudyk,
    That got me looking in the right direction for a C# solution.

    Here is what I came up with for the C# people out there that might need it.
    using Microsoft.Vbe.Interop;
    List<string> proceduresList = new List<string>();
    vbext_ProcKind procType;
    foreach (VBComponent vbc in Globals.ThisWorkbook.VBProject.VBComponents)
       CodeModule codeModule = vbc.CodeModule;
       int currentLine = codeModule.CountOfDeclarationLines + 1;
       while (currentLine < codeModule.CountOfLines)
          string procName = codeModule.get_ProcOfLine(currentLine, out procType);
          currentLine += codeModule.get_ProcCountLines(procName, procType);

    Open in new window


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Suggested Solutions

    A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
    This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
    The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
    Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now