How to add VBA code in Excel using C#

OpenXML doesn’t support macros yet as its Microsoft only feature. So you can’t make use of OpenXML or ClosedXML to add macros. So the only option we have is to open excel programmatically and add VBA code to the workbook using C#.

This post will explain how to open the Excel using C# API and add your VBA code to the sheets programmatically.

We would programmatically add VBA code for code reuse, i.e., automation, or store the macros in Git. This post can help you with your Automation journey 🙂

VBA Macro Types

The below are the macros types that can be added to Excel.

1. Modules (.bas Files)
2. Classes (.cls Files)
3. Forms (.frx & .frm Files)
4. Sheet Macro ( .sheet files)
5. Workbook Macro (ThisWorkbook)

The most used macro types are Modules, classes & sheet macros.

namespace Microsoft.Vbe.Interop
{
    public enum vbext_ComponentType
    {
        vbext_ct_StdModule = 1,
        vbext_ct_ClassModule = 2,
        vbext_ct_MSForm = 3,
        vbext_ct_ActiveXDesigner = 11,
        vbext_ct_Document = 100,
    }
}

Forms are more complicated as they need both UI and back-end logic, so not the right choice to automate.

Here Sheet & WorkbookMacro comes into the section of vbext_ct_Document

Introduction to Excel API using C#

You’ll need to include the following namespaces in your program.

using Microsoft.Office.Interop.Excel;
using Microsoft.Vbe.Interop;

Also, if you give an alias namespace as below, it reduces your program’s wordiness.

using Excel = Microsoft.Office.Interop.Excel;

For opening the excel application, we can use the below command, and also need to turn off visible and screen updating so that it works quickly. Turning it on is only helpful while debugging code.

var excelApp = new Excel.Application();
// visible & screen updating is turned off
excelApp.Visible = false;
excelApp.ScreenUpdating = false;

Now we need to open the Excel workbook for which we need to add macros, etc. Also, make sure that if you’ve created the excel workbook programmatically, it needs to be XLSM and not XLSX. Because XLSX doesn’t support macros, only XLSM does.

One more point to note is that you’ve to give the full path; otherwise, it assumes My Documents as the current directory.

var workbook = excelApp.Workbooks.Open(Environment.CurrentDirectory + "\\Test.xlsm");

Everything related to macros that we want to do in the Excel workbook can be done/accessed by VBProject.

If you want to iterate over all the code files in excel, you can do so by the following code.

foreach(VBComponent codeFile in workbook.VBProject.VBComponents)
{
    Console.WriteLine(codeFile.Name);
    //Your logic here
}

Macro Name need not be the same as worksheet name to which the macro is associated. So to really find out the macro name for the sheet, it is best to iterate through the worksheets and get the CodeName.

foreach(Worksheet sheet in workbook.Worksheets)
{
    Console.WriteLine(sheet.CodeName);
    // Your logic here
}

To update the code file of Component, we need to use CodeModule, which is present in VBComponent.

public interface _CodeModule
{
    CodePane CodePane { get; }
    int CountOfDeclarationLines { get; }
    int CountOfLines { get; }
    string Lines { get; }
    string Name { get; set; }
    VBComponent Parent { get; }
    int ProcBodyLine { get; }
    int ProcCountLines { get; }
    string ProcOfLine { get; }
    int ProcStartLine { get; }
    VBE VBE { get; }

    void AddFromFile(string FileName);
    void AddFromString(string String);
    int CreateEventProc(string EventName, string ObjectName);
    void DeleteLines(int StartLine, int Count = 1);
    bool Find(string Target, ref int StartLine, ref int StartColumn, ref int EndLine, ref int EndColumn, bool WholeWord = False, bool MatchCase = False, bool PatternSearch = False);
    void InsertLines(int Line, string String);
    void ReplaceLine(int Line, string String);
}

There are many useful functions that you can make use of, like InsertLines, DeleteLines, AddFromFile, AddFromString, etc.

Adding or Modifying VBA code using C# for existing components

To delete all code that is already present in the macro component.

VBComponent comp = workbook.VBProject.VBComponents.Item("MacroName");
comp.CodeModule.DeleteLines(1, code.CountOfLines);

To add a macro to a component using a file, we can do the following.

VBComponent comp = workbook.VBProject.VBComponents.Item("MacroName");
comp.CodeModule.AddFromFile(Environment.CurrentDirectory + "\\File.bas");

Or, if you’re planning on writing code on the fly, you can make use of AddFromString.

Adding new Components (Modules/Classes/etc) using C#

Sheet Components are created automatically when a new sheet is added. But if we want to add new modules or classes to the Excel file. We need to add the component first.

VBComponent module = workbook.VBProject.VBComponents.Add(vbext_ComponentType.vbext_ct_StdModule);
module.Name = "Macro1";

Similarly, we can add a VBA class also.

VBComponent classComp = workbook.VBProject.VBComponents.Add(vbext_ComponentType.vbext_ct_ClassModule);
classComp.Name = "Class1";

The added modules/classes will still be blank, and you’ll need to add the code to these using CodeModule as mentioned above.

Conclusion

I hope this gives you all the information that you need to add VBA code to Excel using C#. Also, once you’re done with opening Excel, make sure to close it; otherwise, you’ll have dangling instances open in the background.

workbook.Close(); // After save or saveas to close workbook
excelApp.Quit(); // To close Excel application