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.

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.

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
See also
Next steps
Published: November 13, 2022 | Modified: January 18, 2023 | Author: Ercan Görgülü