VBA Macros in Excel Worksheets

Visual Basic for Applications is human-readable and editable programming code that gets generated when you record a macro. Today, it is widely-used with other Microsoft Office applications such as MS-Word, MS-Excel, and MS-Access.

The VBA Project is where the modules and forms are stored when you write VBA code in Excel.

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

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

Screenshot of Excel VBA module created with GemBox.Spreadsheet

C#
VB.NET

View on GitHub

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

class Program
{
    static void Main()
    {
        // If using 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 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

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

Screenshot of Excel VBA module updated with GemBox.Spreadsheet

C#
VB.NET

View on GitHub

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

class Program
{
    static void Main()
    {
        // If using 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 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

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

Published: November 13, 2022 | Modified: January 18, 2023 | Author: Ercan Görgülü

VBA Macros in Excel Worksheets

See also

Next steps