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