VBA Macros in Excel Worksheets

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

C#
VB.NET

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

Visual Basic for Applications (VBA) is a programming language commonly used in various Microsoft Office applications such as Word, Excel, and Access. It enables creating user-defined functions, automating processes, manipulating user interface features, and working with custom forms or dialog boxes.

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

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.

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

VBA Macros in Excel Worksheets

See also

Next steps