Add VBA macros in Excel worksheets using C# and VB.NET

The following example shows how to create an Excel file with VBA module in C# and VB.NET, using the GemBox.Spreadsheet library.

In GemBox.Spreadsheet a VBA project is represented by the VbaProject element and can be loaded from and saved to macro-enabled (XLSM) file format only.

C#
VB.NET

View on GitHub

using GemBox.Spreadsheet;
using GemBox.Spreadsheet.Vba;

class Program
{
    static void Main()
    {
        // If using the Professional version, put your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");

        var workbook = new ExcelFile();
        var worksheet = workbook.Worksheets.Add("Sheet1");

        // Create the module.
        VbaModule vbaModule = workbook.VbaProject.Modules.Add(worksheet);
        vbaModule.Code =
@"Sub Button1_Click()
    MsgBox ""Hello World!""
End Sub";

        // Create a button to assign macro.
        var button = worksheet.FormControls.AddButton("Click Me!", "B2", 100, 15, LengthUnit.Point);
        // Assign the macro.
        button.SetMacro(vbaModule, "Button1_Click");

        // Save the workbook as macro-enabled Excel file.
        workbook.Save("AddVbaModule.xlsm");
    }
}

Imports GemBox.Spreadsheet
Imports GemBox.Spreadsheet.Vba

Module Program

    Sub Main()

        ' If using the Professional version, put your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")

        Dim workbook As New ExcelFile()
        Dim worksheet = workbook.Worksheets.Add("Sheet1")

        ' Create the module.
        Dim vbaModule As VbaModule = workbook.VbaProject.Modules.Add(worksheet)
        vbaModule.Code =
"Sub Button1_Click()
    MsgBox ""Hello World!""
End Sub"

        ' Create a button to assign macro.
        Dim button = worksheet.FormControls.AddButton("Click Me!", "B2", 100, 15, LengthUnit.Point)
        ' Assign the macro.
        button.SetMacro(vbaModule, "Button1_Click")

        ' Save the workbook as macro-enabled Excel file.
        workbook.Save("AddVbaModule.xlsm")
    End Sub
End Module

Screenshot of Excel VBA module created with GemBox.Spreadsheet

The example below shows how you can load an Excel file with a VBA module, update it, and save it to a new XLSM file.

Updating an Excel VBA module

The following example shows how you can update an Excel VBA module in C# and VB.NET.

C#
VB.NET

View on GitHub

using GemBox.Spreadsheet;
using GemBox.Spreadsheet.Vba;

class Program
{
    static void Main()
    {
        // If using the Professional version, put your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");

        var workbook = ExcelFile.Load("%#SampleVba.xlsm%");

        // Get the module.
        VbaModule vbaModule = workbook.VbaProject.Modules["Module1"];
        // Update text for the popup message.
        vbaModule.Code = vbaModule.Code.Replace("Hello world!", "Hello from GemBox.Spreadsheet!");

        workbook.Save("UpdateVbaModule.xlsm");
    }
}

Imports GemBox.Spreadsheet
Imports GemBox.Spreadsheet.Vba

Module Program

    Sub Main()

        ' If using the Professional version, put your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")

        Dim workbook = ExcelFile.Load("%#SampleVba.xlsm%")

        ' Get the module.
        Dim vbaModule As VbaModule = workbook.VbaProject.Modules("Module1")
        ' Update text for the popup message.
        vbaModule.Code = vbaModule.Code.Replace("Hello world!", "Hello from GemBox.Spreadsheet!")

        workbook.Save("UpdateVbaModule.xlsm")
    End Sub
End Module

Screenshot of an Excel VBA module updated with GemBox.Spreadsheet

Next steps

GemBox.Spreadsheet is a .NET component that enables you to read, write, edit, convert, and print spreadsheet files from your .NET applications using one simple API.

Download Buy

Add VBA macros in Excel worksheets using C# and VB.NET

Updating an Excel VBA module

See also

Next steps