How to Retrieve Macro Name Using VSTO in C#

Introduction

This article shows how to get a Macro Name using VSTO in C#. This articles starts with an introduction of the display macro in a drop down using Excel VSTO. We know that it is not a regular requirement in our project and it is quite difficult to find the solution on the internet.

What a Macro is

A Macro is a set of instructions in a Visual Basic module that will make Excel perform commands and actions for you by running them. Excel can repeat a task at any time using the macro. They allow you to do repetitive and complex tasks that you perform regularly. The easiest way to create a macro is to record a macro, Excel stores information about each step you take as you perform a series of commands.

How to create a Macro in Excel

If you are unfamiliar with Macros then please refer to the following link to create a Macro.

Quick start: Create a macro

Create a macro

Create a C# application to list the macros in the workbook :

I have created a sample to get a macro name using VSTO C#.

1. Create VSTO project in C#

Create a new VSTO project in C# as shown in following screen shot.

How to create Add-Ins using VSTO

Read my following article about creating Add-ins using VSTO :

How to Create Add-Ins Using VSTO in MS-Word

I have created Excel add-ins. I have added a ribbon button in the Ribbon tab. I open a Windows Forms form from the Ribbon button.

 

2. Add Windows Forms form to display the Macro in a DropDown list

After creating the VSTO project, you can add a Windows Forms form as shown in the following screen shot. I have added a drop down control to this form. I have filled in a Macro name in the drop down control.



3. Get Macro Name from Excel using C#

I have created a method to get a Macro name from an Excel file.

On the Project menu, click "References". In the References dialog box, select the following references “Microsoft.Vbe.Interop”.



Create a method to get a Macro name from Excel as shown in the following code.

/// <summary>

/// Get Macro list

/// </summary>

/// <returns></returns>

public static List<string> GetMacroList()

{

        List<string> macroList = new List<string>();

        vbext_ProcKind prockind = vbext_ProcKind.vbext_pk_Proc;

        string curMacro = string.Empty;

        string newMacro = string.Empty;

        Microsoft.Office.Interop.Excel.Workbook wb = Globals.ThisAddIn.Application.ActiveWorkbook;           

        try

        {

            foreach (VBProject pj in wb.Application.VBE.VBProjects)

            {

                 if (pj.Protection == vbext_ProjectProtection.vbext_pp_none)

                {

                        foreach (VBComponent vbcomp in pj.VBComponents)

                        {

                            if (vbcomp.CodeModule.CountOfLines > 0)

                            {

                                for (int i = 1; i < vbcomp.CodeModule.CountOfLines - 1; i++)

                                {

                                    newMacro = vbcomp.CodeModule.get_ProcOfLine(i, out prockind);

                                    string str = vbcomp.Name;

 

                                    if (curMacro != newMacro)

                                    {

                                        curMacro = newMacro;

                                        macroList.Add(str + "." + curMacro);

                                    }

                                }

                            }

                        }

                    }

                }

            }

           catch (Exception ex)

           {

                MessageBox.Show(ex.Message.ToString());

           }           

          return macroList;

  }

After creating a method, fill in the drop down as shown in the following code. Display the macro name in this drop down.

public MacroList()

{

    InitializeComponent();
    cmbMacroList.DataSource = GetMacroList();

}

4. Create Excel workbook

Create an Excel workbook with two macros as in the following:

1. Start Excel. A new blank workbook is created.
2. Press ALT+F11 to start the Visual Basic Editor.
3. In Project Explorer, double-click "ThisWorkbook" to start the code editor.
4. Paste the following code for two simple macros in the code editor:
   
   Option Explicit
   
   Sub Macro_A()
   MsgBox "This is Macro A"
   End Sub
   
   Sub Macro_B()
   MsgBox "This is Macro B"
   End Sub
    
5. Close the Visual Basic Editor, and return to the spreadsheet view.
6. In Excel 2003 and in earlier versions of Excel, save the workbook as C:\Abc.xls. In Excel 2007, save the workbook as a macro-enabled workbook named C:\Abc.xlsm.
7. Close the workbook and quit Excel.

5. Output of the sample

1. Build and run the application.
2. There are two way to test this sample. When you run this sample, by default Excel is opened. You can add the macro and see the macro name in the drop down. The second option is, since you have already saved the macro file on your machine, click on "Macro add-ins" and see the macro name in the drop-down.

 

Prerequisites

The following are the prerequisites for running the downloaded sample:

 

VSTO/VSTO runtime version	Develop/Build against Office 2010	.Net Framework	Version Visual Studio
VSTO 4.0	Word, Excel, Outlook, PowerPoint, Visio, InfoPath, Project	.NET 3.5 or 4.0	Built into Visual Studio 2010 Professional and above