Working with Macros in Syncfusion Excel library

7 Nov 202324 minutes to read

Macro is a set of process that can be run repeatedly in Excel document.

Creating a Macro

XlsIO allows to create macros in Excel document through IVbaProject interface. The macro document can be saved into different formats such as XLS, XLTM and XLSM.
XlsIO supports creating macro in following types using VbaModuleType enum.

Document
StdModule
Class
MsForm

You can add a Vba module through IVbaModules interface in XlsIO.

Document

Document is the default module type which will be added for every worksheet and one for entire workbook while creating VbaProject.

C# [Cross-platform]
C# [Windows-specific]
VB.NET [Windows-specific]
//Adding Document to the workbook
IVbaProject project = workbook.VbaProject;
IVbaModule module = project.Modules.Add("Document ", VbaModuleType. Document);
//Adding Document to the workbook
IVbaProject project = workbook.VbaProject;
IVbaModule module = project.Modules.Add("Document", VbaModuleType.Document);
//Adding Document to the workbook
Dim project As IVbaProject = workbook.VbaProject
Dim [module] As IVbaModule = project.Modules.Add("Document ", VbaModuleType. Document)

The following code illustrate how to use Document module in Excel document.

C# [Cross-platform]
C# [Windows-specific]
VB.NET [Windows-specific]
using (ExcelEngine excelEngine = new ExcelEngine())
{
  //Instantiate the excel application object.
  IApplication application = excelEngine.Excel;

  // Creating new workbook
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet sheet = workbook.Worksheets[0];

  //Creating Vba project
  IVbaProject project = workbook.VbaProject;

  //Accessing vba modules collection
  IVbaModules vbaModules = project.Modules;

  // Accessing sheet module
  IVbaModule vbaModule = vbaModules[sheet.CodeName];

  //Adding vba code to the module
  vbaModule.Code = "Sub Auto_Open\n MsgBox \" Workbook Opened \" \n End Sub";

  //Saving as macro document
  FileStream output = new FileStream("Output.xlsm", FileMode.Create, FileAccess.ReadWrite);
  workbook.SaveAs(output, ExcelSaveType.SaveAsMacro);
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  //Instantiate the excel application object.
  IApplication application = excelEngine.Excel;

  // Creating new workbook
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet sheet = workbook.Worksheets[0];

  //Creating Vba project
  IVbaProject project = workbook.VbaProject;

  //Accessing vba modules collection
  IVbaModules vbaModules = project.Modules;

  //Accessing sheet module
  IVbaModule vbaModule = vbaModules[sheet.CodeName];

  //Adding vba code to the module
  vbaModule.Code = "Sub Auto_Open\n MsgBox \"Workbook Opened\" \n End Sub";

  //Saving as macro document
  workbook.SaveAs("Output.xlsm");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  'Instantiate the excel application object.
  Dim application As IApplication = excelEngine.Excel

  'Creating new Workbook
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim sheet As IWorksheet = workbook.Worksheets(0)

  'Creating Vba project
  Dim project As IVbaProject = workbook.VbaProject

  'Accessing vba modules collection
  Dim vbaModules As IVbaModules = project.Modules

  ‘Accessing sheet module
  Dim vbaModule As IVbaModule = vbaModules(sheet.CodeName)

  'Adding vba code to the module
  vbaModule.Code = "Sub Auto_Open" & vbLf & " MsgBox "" Workbook Opened "" " & vbLf & " End Sub"

  'Saving as macro document
  workbook.SaveAs("Output.xlsm")
End Using

A complete working example to create macro as document in C# is present on this GitHub page.

The Vba project in the output looks like below.

working with macros

The macro output in the Excel document looks like below.

working with macros

StdModule

StdModule is the module created for whenever a macro process is recorded in Excel document.

The following code illustrate how to add a StdModule using Add method. Here, the parameters name and VbaModuleType enum value is used.

Test – Macro module name added to the Vba project.
StdModule – Type of the Vba module.

C# [Cross-platform]
C# [Windows-specific]
VB.NET [Windows-specific]
//Adding StdModule to the workbook
IVbaProject project = workbook.VbaProject;
IVbaModule module = project.Modules.Add("Test", VbaModuleType.StdModule);
//Adding StdModule to the workbook
IVbaProject project = workbook.VbaProject;
IVbaModule module = project.Modules.Add("Test", VbaModuleType.StdModule);
//Adding StdModule to the workbook
Dim project As IVbaProject = workbook.VbaProject
Dim [module] As IVbaModule = project.Modules.Add("Test", VbaModuleType.StdModule)

The following code illustrate how to create a macro using StdModule in Excel document.

C# [Cross-platform]
C# [Windows-specific]
VB.NET [Windows-specific]
using (ExcelEngine excelEngine = new ExcelEngine())
{
  //Instantiate the excel application object.
  IApplication application = excelEngine.Excel;

  // Creating new workbook
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet sheet = workbook.Worksheets[0];

  //Creating Vba project
  IVbaProject project = workbook.VbaProject;

  //Accessing vba modules collection
  IVbaModules vbaModules = project.Modules;

  //Adding a vba module
  IVbaModule vbaModule = vbaModules.Add("StdModule", VbaModuleType.StdModule);

  //Adding vba code to the module
  vbaModule.Code = "Sub Auto_Open\n MsgBox \"Macro Added\" \n End Sub";

  //Saving as macro document
  FileStream output = new FileStream("Output.xlsm", FileMode.Create, FileAccess.ReadWrite);
  workbook.SaveAs(output, ExcelSaveType.SaveAsMacro);
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  //Instantiate the excel application object.
  IApplication application = excelEngine.Excel;

  // Creating new workbook
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet sheet = workbook.Worksheets[0];

  //Creating Vba project
  IVbaProject project = workbook.VbaProject;

  //Accessing vba modules collection
  IVbaModules vbaModules = project.Modules;

  //Adding a vba module
  IVbaModule vbaModule = vbaModules.Add("StdModule", VbaModuleType.StdModule);

  //Adding vba code to the module
  vbaModule.Code = "Sub Auto_Open\n MsgBox \"Macro Added\" \n End Sub";

  //Saving as macro document
  workbook.SaveAs("Output.xlsm");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  'Instantiate the excel application object.
  Dim application As IApplication = excelEngine.Excel

  'Creating new Workbook
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim sheet As IWorksheet = workbook.Worksheets(0)

  'Creating Vba project
  Dim project As IVbaProject = workbook.VbaProject

  'Accessing vba modules collection
  Dim vbaModules As IVbaModules = project.Modules

  'Adding a vba module
  Dim vbaModule As IVbaModule = vbaModules.Add("StdModule", VbaModuleType.StdModule)

  'Adding vba code to the module
  vbaModule.Code = "Sub Auto_Open" & vbLf & " MsgBox ""Macro Added"" " & vbLf & " End Sub"

  'Saving as macro document
  workbook.SaveAs("Output.xlsm")
End Using

A complete working example to create macro as standard module in C# is present on this GitHub page.

The Vba project in the output Excel document looks like below.

working with macros

The Macro output in the Excel document looks like below.

working with macros

Class

Class module allows us to create our own object model to use it where same kind of objects needs to be added with different values such as creating the employee information list. XlsIO supports creating a class module in Excel document.

The following code illustrate how to add a class in XlsIO. Here, the parameters name and VbaModuleType enum value is used.

Test – Class name used in the Vba project
ClassModule – Type of Vba module

C# [Cross-platform]
C# [Windows-specific]
VB.NET [Windows-specific]
//Adding class module to the workbook
IVbaProject project = workbook.VbaProject;
IVbaModule module = project.Modules.Add("Test", VbaModuleType.ClassModule);
//Adding class module to the workbook
IVbaProject project = workbook.VbaProject;
IVbaModule module = project.Modules.Add("Test", VbaModuleType.ClassModule);
//Adding class module to the workbook
Dim project As IVbaProject = workbook.VbaProject
Dim [module] As IVbaModule = project.Modules.Add("Test", VbaModuleType.ClassModule)

The following code illustrate how to use class module to run a macro with another module in Excel document.

C# [Cross-platform]
C# [Windows-specific]
VB.NET [Windows-specific]
using (ExcelEngine excelEngine = new ExcelEngine())
{
  //Instantiate the excel application object.
  IApplication application = excelEngine.Excel;

  // Creating new workbook
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet sheet = workbook.Worksheets[0];

  //Creating Vba project
  IVbaProject project = workbook.VbaProject;

  //Accessing vba modules collection
  IVbaModules vbaModules = project.Modules;

  //Adding a class module
  IVbaModule clsModule = vbaModules.Add("Test", VbaModuleType.ClassModule);
  clsModule.Code = "Public Sub Create()\n MsgBox \"Created a class module\" \n End Sub";

  //Adding a vba module
  IVbaModule vbaModule = vbaModules.Add("Module1", VbaModuleType.StdModule);

  //Using class in StdModule
  vbaModule.Code = "Sub Auto_Open()\n Dim obj As New test \n obj.Create \n End Sub";

  //Saving as macro document
  FileStream output = new FileStream("Output.xlsm", FileMode.Create, FileAccess.ReadWrite);
  workbook.SaveAs(output, ExcelSaveType.SaveAsMacro);
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  //Instantiate the excel application object.
  IApplication application = excelEngine.Excel;

  // Creating new workbook
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet sheet = workbook.Worksheets[0];

  //Creating Vba project
  IVbaProject project = workbook.VbaProject;

  //Accessing vba modules collection
  IVbaModules vbaModules = project.Modules;

  //Adding a class module
  IVbaModule clsModule = vbaModules.Add("Test", VbaModuleType.ClassModule);
  clsModule.Code = "Public Sub Create()\n MsgBox \"Created a class module\" \n End Sub";

  //Adding a vba module
  IVbaModule vbaModule = vbaModules.Add("Module1", VbaModuleType.StdModule);

  //Using class in StdModule
  vbaModule.Code = "Sub Auto_Open()\n Dim obj As New test \n obj.Create \n End Sub";

  //Saving as macro document
  workbook.SaveAs("Output.xlsm");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  'Instantiate the excel application object.
  Dim application As IApplication = excelEngine.Excel

  'Creating new Workbook
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim sheet As IWorksheet = workbook.Worksheets(0)

  'Creating Vba project
  Dim project As IVbaProject = workbook.VbaProject

  'Accessing vba modules collection
  Dim vbaModules As IVbaModules = project.Modules

  'Adding a vba module
  Dim vbaModule As IVbaModule = vbaModules.Add("StdModule", VbaModuleType.StdModule)

  'Adding vba code to the module
  vbaModule.Code = "Sub Auto_Open" & vbLf & " MsgBox ""Macro Added"" " & vbLf & " End Sub"

  'Saving as macro document
  workbook.SaveAs("Output.xlsm")
End Using

A complete working example to create macro as class in C# is present on this GitHub page.

The Vba project in the output Excel document looks like below.

working with macros

The Macro output in the Excel document looks like below.

working with macros

MsForm

MsForm is the form module in which we can have form controls such as textbox, label, buttons etc. Form module cannot be created by XlsIO, but it allows to copy from a form module existing another workbook to new workbook.

The following code illustrate how to add a class in XlsIO. Here, the parameters name and VbaModuleType enum value is used.

UserForm – Class name used in the Vba project
MsForm – Type of Vba module

C# [Cross-platform]
C# [Windows-specific]
VB.NET [Windows-specific]
//Adding class module to the workbook
IVbaProject project = workbook.VbaProject;
IVbaModule module = project.Modules.Add("UserForm", VbaModuleType.MsForm);
//Adding class module to the workbook
IVbaProject project = workbook.VbaProject;
IVbaModule module = project.Modules.Add("UserForm", VbaModuleType.MsForm);
//Adding class module to the workbook
Dim project As IVbaProject = workbook.VbaProject
Dim [module] As IVbaModule = project.Modules.Add("UserForm", VbaModuleType.MsForm)

The following code illustrate how to copy a form from another workbook to new workbook.

C# [Cross-platform]
C# [Windows-specific]
VB.NET [Windows-specific]
using (ExcelEngine excelEngine = new ExcelEngine())
{
  //Instantiate the excel application object.
  IApplication application = excelEngine.Excel;

  // Creating new workbook
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet sheet = workbook.Worksheets[0];

  //Creating Vba project
  IVbaProject project = workbook.VbaProject;

  //Accessing vba modules collection
  IVbaModules vbaModules = project.Modules;

  //Opening form module existing workbook
  FileStream input = new FileStream("Test.xls", FileMode.Open, FileAccess.ReadWrite);
  IWorkbook newBook = application.Workbooks.Open(input);

  IVbaProject newProject = newBook.VbaProject;

  //Accessing existing form module
  IVbaModule form = newProject.Modules["UserForm1"];

  //Adding a form module in new workbook
  IVbaModule formModule = project.Modules.Add(form.Name, VbaModuleType.MsForm);

  //Copying the form code behind
  formModule.Code = form.Code;

  //Copying the designer of the form
  formModule.DesignerStorage = form.DesignerStorage;

  //Saving as macro document
  FileStream output = new FileStream("Output.xlsm", FileMode.Create, FileAccess.ReadWrite);
  workbook.SaveAs(output, ExcelSaveType.SaveAsMacro);
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  //Instantiate the excel application object.
  IApplication application = excelEngine.Excel;

  // Creating new workbook
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet sheet = workbook.Worksheets[0];

  //Creating Vba project
  IVbaProject project = workbook.VbaProject;

  //Accessing vba modules collection
  IVbaModules vbaModules = project.Modules;

  //Opening form module existing workbook
  IWorkbook newBook = application.Workbooks.Open("Test.xls");
  IVbaProject newProject = newBook.VbaProject;

  //Accessing existing form module
  IVbaModule form = newProject.Modules["UserForm1"];

  //Adding a form module in new workbook
  IVbaModule formModule = project.Modules.Add(form.Name, VbaModuleType.MsForm);

  //Copying the form code behind
  formModule.Code = form.Code;

  //Copying the designer of the form
  formModule.DesignerStorage = form.DesignerStorage;

  //Saving as macro document
  workbook.SaveAs("Output.xlsm");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  'Instantiate the excel application object.
  Dim application As IApplication = excelEngine.Excel

  'Creating new Workbook
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim sheet As IWorksheet = workbook.Worksheets(0)

  'Creating Vba project
  Dim project As IVbaProject = workbook.VbaProject

  'Accessing vba modules collection
  Dim vbaModules As IVbaModules = project.Modules

  'Open form existing workbook
  Dim newBook As IWorkbook = application.Workbooks.Open("Test.xls")
  Dim newProject As IVbaProject = newBook.VbaProject

  'Accessing existing form module
  Dim form As IVbaModule = newProject.Modules("UserForm1")

  'Adding a form module in new workbook
  Dim formModule As IVbaModule = project.Modules.Add(form.Name, VbaModuleType.MsForm)

  'Copying the form code behind
  formModule.Code = form.Code

  'Copying the designer of the form
  formModule.DesignerStorage = form.DesignerStorage

  'Saving as macro document
  workbook.SaveAs("Output.xlsm")
End Using

A complete working example to create macro as MS Form in C# is present on this GitHub page.

The Vba project in the output Excel document looks like below.

working with macros

Assigning Macro to Shapes

XlsIO supports assigning macros to the shape controls in the Excel document through OnAction property.

The following code illustrate how to assign macros to shapes in Excel document.

C# [Cross-platform]
C# [Windows-specific]
VB.NET [Windows-specific]
using (ExcelEngine excelEngine = new ExcelEngine())
{
  //Instantiate the excel application object.
  IApplication application = excelEngine.Excel;

  // Creating new workbook
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet sheet = workbook.Worksheets[0];

  //Creating Vba project
  IVbaProject project = workbook.VbaProject;

  //Accessing vba modules collection
  IVbaModules vbaModules = project.Modules;

  //Adding a vba module
  IVbaModule vbaModule = vbaModules.Add("StdModule", VbaModuleType.StdModule);

  //Adding vba code to the module
  vbaModule.Code = "Sub Invoke()\n MsgBox \"Macro Added\" \n End Sub";

  //Adding a auto shape
  IShape shape = sheet.Shapes.AddAutoShapes(AutoShapeType.Rectangle, 1, 2, 60, 70);
  shape.Name = "Shape1";

  //Assigning a Macro to shape
  shape.OnAction = "StdModule.Invoke";

  //Saving as macro document
  FileStream output = new FileStream("Output.xlsm", FileMode.Create, FileAccess.ReadWrite);
  workbook.SaveAs(output, ExcelSaveType.SaveAsMacro);
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  //Instantiate the excel application object.
  IApplication application = excelEngine.Excel;

  // Creating new workbook
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet sheet = workbook.Worksheets[0];

  //Creating Vba project
  IVbaProject project = workbook.VbaProject;

  //Accessing vba modules collection
  IVbaModules vbaModules = project.Modules;

  //Adding a vba module
  IVbaModule vbaModule = vbaModules.Add("StdModule", VbaModuleType.StdModule);

  //Adding vba code to the module
  vbaModule.Code = "Sub Invoke()\n MsgBox \"Macro Added\" \n End Sub";

  //Adding a auto shape
  IShape shape = sheet.Shapes.AddAutoShapes(AutoShapeType.Rectangle, 1, 2, 60, 70);
  shape.Name = "Shape1";

  //Assigning a Macro to shape
  shape.OnAction = "StdModule.Invoke";

  //Saving as macro document
  workbook.SaveAs("Output.xlsm");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  'Instantiate the excel application object.
  Dim application As IApplication = excelEngine.Excel

  'Creating new Workbook
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim sheet As IWorksheet = workbook.Worksheets(0)

  'Creating Vba project
  Dim project As IVbaProject = workbook.VbaProject

  'Accessing vba modules collection
  Dim vbaModules As IVbaModules = project.Modules

  'Adding a vba module
  Dim vbaModule As IVbaModule = vbaModules.Add("StdModule", VbaModuleType.StdModule)

  'Adding vba code to the module
  vbaModule.Code = "Sub Invoke()" & vbLf & " MsgBox ""Macro Added"" " & vbLf & " End Sub"

  'Adding a auto shape
  Dim shape As IShape = sheet.Shapes.AddAutoShapes(AutoShapeType.Rectangle, 1, 2, 60, 70)

  shape.Name = "Shape1"

  'Assigning a Macro to shape
  shape.OnAction = "StdModule.Invoke"

  'Saving as macro document
  workbook.SaveAs("Output.xlsm")
End Using

A complete working example to assign macro to shape in C# is present on this GitHub page.

When the shape is clicked, the output looks like below.

working with macros

Saving macro enabled document into stream

By default, while saving the Excel workbook into stream, the file type will be based on the Excel version used. For Excel97to2003 version, the file format will be XLS type. Above this version, the document will be saved as XLSX format. So, while saving the macro enabled documents into XLSM and XLTM formats into stream, the ExcelSaveType should be provided as SaveAsMacro and SaveAsMacroTemplate.

The following code illustrate how to save macro-enabled documents into stream.

C# [Cross-platform]
C# [Windows-specific]
VB.NET [Windows-specific]
using (ExcelEngine excelEngine = new ExcelEngine())
{
  //Instantiate the excel application object.
  IApplication application = excelEngine.Excel;

  // Creating new workbook
  IWorkbook workbook = application.Workbooks.Create(1);
  workbook.Version = ExcelVersion.Excel2016;
  IWorksheet sheet = workbook.Worksheets[0];

  //Creating Vba project
  IVbaProject project = workbook.VbaProject;

  //Accessing vba modules collection
  IVbaModules vbaModules = project.Modules;

  //Adding a vba module
  IVbaModule vbaModule = vbaModules.Add("StdModule", VbaModuleType.StdModule);

  //Adding vba code to the module
  vbaModule.Code = "Sub Auto_Open\n MsgBox \"Macro Added\" \n End Sub";

  //Saving as Macro in XLTM format
  MemoryStream stream = new MemoryStream();
  workbook.SaveAs(stream, ExcelSaveType.SaveAsMacroTemplate);
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  //Instantiate the excel application object.
  IApplication application = excelEngine.Excel;

  // Creating new workbook
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet sheet = workbook.Worksheets[0];

  //Creating Vba project
  IVbaProject project = workbook.VbaProject;

  //Accessing vba modules collection
  IVbaModules vbaModules = project.Modules;

  //Adding a vba module
  IVbaModule vbaModule = vbaModules.Add("StdModule", VbaModuleType.StdModule);

  //Adding vba code to the module
  vbaModule.Code = "Sub Auto_Open\n MsgBox \"Macro Added\" \n End Sub";

  //Saving as Macro in XLSM format
  MemoryStream stream = new MemoryStream();
  workbook.SaveAs(stream, ExcelSaveType.SaveAsMacro);
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  'Instantiate the excel application object.
  Dim application As IApplication = excelEngine.Excel

  'Creating new Workbook
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim sheet As IWorksheet = workbook.Worksheets(0)

  'Creating Vba project
  Dim project As IVbaProject = workbook.VbaProject

  'Accessing vba modules collection
  Dim vbaModules As IVbaModules = project.Modules

  'Adding a vba module
  Dim vbaModule As IVbaModule = vbaModules.Add("StdModule", VbaModuleType.StdModule)

  'Adding vba code to the module
  vbaModule.Code = "Sub Auto_Open" & vbLf & " MsgBox ""Macro Added"" " & vbLf & " End Sub"

  ‘Saving as Macro in XLSM format
  Dim stream As MemoryStream = New MemoryStream()
  workbook.SaveAs(stream, ExcelSaveType.SaveAsMacro)
End Using

A complete working example to save macro enabled document into stream in C# is present on this GitHub page.

Editing a Macro

XlsIO allows to edit the existing macros in the Excel documents. To edit macros in Excel document, the module containing the macro code needs to be modified. By using the name of the module, it can be accessed and edited in XlsIO.

The following code illustrate how to edit existing macro in Excel document.

C# [Cross-platform]
C# [Windows-specific]
VB.NET [Windows-specific]
using (ExcelEngine excelEngine = new ExcelEngine())
{
  //Instantiate the excel application object.
  IApplication application = excelEngine.Excel;

  //Opening form module existing workbook
  FileStream input = new FileStream("Test.xls", FileMode.Open, FileAccess.ReadWrite);
  IWorkbook workbook = application.Workbooks.Open(input);
  IWorksheet sheet = workbook.Worksheets[0];

  //Accessing Vba project
  IVbaProject project = workbook.VbaProject;

  //Accessing vba modules collection
  IVbaModules vbaModules = project.Modules;

  //Access a Vba Module
  IVbaModule vbaModule = vbaModules["Module1"];

  //Edit the macro
  vbaModule.Name = "CreateData";
  vbaModule.Code = "Sub Auto_Open()\n MsgBox \"Macro is edited\" \n End Sub ";

  //Saving as macro document
  FileStream output = new FileStream("Output.xlsm", FileMode.Create, FileAccess.ReadWrite);
  workbook.SaveAs(output, ExcelSaveType.SaveAsMacro);

  input.Close();
  output.Close();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  // Instantiate the excel application object.
  IApplication application = excelEngine.Excel;

  // Opening a workbook
  IWorkbook workbook = application.Workbooks.Open("Test.xls");
  workbook.Version = ExcelVersion.Excel2016;
  IWorksheet sheet = workbook.Worksheets[0];

  //Accessing Vba project
  IVbaProject project = workbook.VbaProject;

  //Accessing vba modules collection
  IVbaModules vbaModules = project.Modules;

  //Access a Vba Module
  IVbaModule vbaModule = vbaModules["Module1"];

  //Edit the macro
  vbaModule.Name = "CreateData";
  vbaModule.Code = "Sub Auto_Open()\n MsgBox \"Macro is edited\" \n End Sub ";

  //Saving as macro document
  workbook.SaveAs("Output.xlsm");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  'Instantiate the excel application object.
  Dim application As IApplication = excelEngine.Excel

  'Opening a Workbook
  Dim workbook As IWorkbook = application.Workbooks.Open("Test.xls")
  Dim sheet As IWorksheet = workbook.Worksheets(0)

  'Creating Vba project
  Dim project As IVbaProject = workbook.VbaProject

  'Accessing vba modules collection
  Dim vbaModules As IVbaModules = project.Modules

  'Accessing a vba module
  Dim vbaModule As IVbaModule = vbaModules("Module1")

  'Editing a module
  vbaModule.Name = "CreateData"
  vbaModule.Code = "Sub Auto_Open()" & vbLf & " MsgBox ""Macro is edited"" " & vbLf & " End Sub "

  'Saving as macro document
  workbook.SaveAs("Output.xlsm")
End Using

A complete working example to edit macro in C# is present on this GitHub page.

The Vba project in the output Excel document looks like below.

working with macros

The Macro output in the Excel document looks like below.

working with macros

NOTE

Macros are parsed only when accessed. By default, opening and saving a macro file will preserve its macros.

Removing Macros

Excel macro-enabled documents can be saved as normal documents where the macro process is not necessary. XlsIO supports saving the macro-enabled documents such as XLS, XLSM, XLTM without macros and normal documents such as XLSX and XLS. For that, macro in the Excel documents needs to be removed.

Macro in the Excel document can be removed in the following ways.

Remove(String name)
RemoveAt(int index)
Clear()
SkipOnSave

Remove(string name)

Macro process exist in the Vba project’s code modules. To remove a macro, the Vba modules needs to be removed.

name – Name of the Vba module needs to be removed.

The following code illustrate how to remove a module using Remove method.

C# [Cross-platform]
C# [Windows-specific]
VB.NET [Windows-specific]
using (ExcelEngine excelEngine = new ExcelEngine())
{
  //Instantiate the excel application object.
  IApplication application = excelEngine.Excel;

  //Opening form module existing workbook
  FileStream input = new FileStream("Test.xls", FileMode.Open, FileAccess.ReadWrite);
  IWorkbook workbook = application.Workbooks.Open(input);
  IWorksheet sheet = workbook.Worksheets[0];

  //Accessing Vba project
  IVbaProject project = workbook.VbaProject;

  //Accessing vba modules collection
  IVbaModules vbaModules = project.Modules;

  //Remove macro module
  vbaModules.Remove("Module1");

  //Saving as macro document
  FileStream output = new FileStream("Output.xlsm", FileMode.Create, FileAccess.ReadWrite);
  workbook.SaveAs(output, ExcelSaveType.SaveAsMacro);

  input.Close();
  output.Close();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  // Instantiate the excel application object.
  IApplication application = excelEngine.Excel;

  // Opening a workbook
  IWorkbook workbook = application.Workbooks.Open("Test.xls");
  workbook.Version = ExcelVersion.Excel2016;
  IWorksheet sheet = workbook.Worksheets[0];

  //Accessing Vba project
  IVbaProject project = workbook.VbaProject;

  //Accessing vba modules collection
  IVbaModules vbaModules = project.Modules;

  //Remove macro module
  vbaModules.Remove("Module1");

  //Saving as macro document
  workbook.SaveAs("Output.xlsm");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  'Instantiate the excel application object.
  Dim application As IApplication = excelEngine.Excel

  'Opening a Workbook
  Dim workbook As IWorkbook = application.Workbooks.Open("Test.xls")
  Dim sheet As IWorksheet = workbook.Worksheets(0)

  'Creating Vba project
  Dim project As IVbaProject = workbook.VbaProject

  'Accessing vba modules collection
  Dim vbaModules As IVbaModules = project.Modules

  //Remove macro module
  vbaModules.Remove("Module1")

  'Saving as macro document
  workbook.SaveAs("Output.xlsm")
End Using

A complete working example to remove macro using name in C# is present on this GitHub page.

RemoveAt(int index)

Vba module can be removed using the position from the IVbaModules collection.

Index – Position of the Vba module in the modules collection.

The following code illustrate how to remove a macro using module index.

C# [Cross-platform]
C# [Windows-specific]
VB.NET [Windows-specific]
using (ExcelEngine excelEngine = new ExcelEngine())
{
  //Instantiate the excel application object.
  IApplication application = excelEngine.Excel;

  //Opening form module existing workbook
  FileStream input = new FileStream("Test.xls", FileMode.Open, FileAccess.ReadWrite);
  IWorkbook workbook = application.Workbooks.Open(input);
  IWorksheet sheet = workbook.Worksheets[0];

  //Accessing Vba project
  IVbaProject project = workbook.VbaProject;

  //Accessing vba modules collection
  IVbaModules vbaModules = project.Modules;

  //Remove macro module
  vbaModules.RemoveAt(0);

  //Saving as macro document
  FileStream output = new FileStream("Output.xlsm", FileMode.Create, FileAccess.ReadWrite);
  workbook.SaveAs(output, ExcelSaveType.SaveAsMacro);
  input.Close();
  output.Close();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  // Instantiate the excel application object.
  IApplication application = excelEngine.Excel;

  // Opening a workbook
  IWorkbook workbook = application.Workbooks.Open("Test.xls");
  workbook.Version = ExcelVersion.Excel2016;
  IWorksheet sheet = workbook.Worksheets[0];

  //Accessing Vba project
  IVbaProject project = workbook.VbaProject;

  //Accessing vba modules collection
  IVbaModules vbaModules = project.Modules;

  //Remove macro module
  vbaModules.RemoveAt(0);

  //Saving as macro document
  workbook.SaveAs("Output.xlsm");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  'Instantiate the excel application object.
  Dim application As IApplication = excelEngine.Excel

  'Opening a Workbook
  Dim workbook As IWorkbook = application.Workbooks.Open("Test.xls")
  Dim sheet As IWorksheet = workbook.Worksheets(0)

  'Creating Vba project
  Dim project As IVbaProject = workbook.VbaProject

  'Accessing vba modules collection
  Dim vbaModules As IVbaModules = project.Modules

  //Remove macro module
  vbaModules.RemoveAt(0)

  'Saving as macro document
  workbook.SaveAs("Output.xlsm")
End Using

A complete working example to remove macro using index in C# is present on this GitHub page.

Clear()

Clear() method removes all the Vba modules at once by clearing the module collection.

The following code illustrate how to remove all macros using Clear method.

C# [Cross-platform]
C# [Windows-specific]
VB.NET [Windows-specific]
using (ExcelEngine excelEngine = new ExcelEngine())
{
  //Instantiate the excel application object.
  IApplication application = excelEngine.Excel;

  //Opening form module existing workbook
  FileStream input = new FileStream("Test.xls", FileMode.Open, FileAccess.ReadWrite);
  IWorkbook workbook = application.Workbooks.Open(input);
  IWorksheet sheet = workbook.Worksheets[0];

  //Accessing Vba project
  IVbaProject project = workbook.VbaProject;

  //Accessing vba modules collection
  IVbaModules vbaModules = project.Modules;

  //Remove all macros
  vbaModules.Clear();

  //Saving as macro document
  FileStream output = new FileStream("Output.xlsm", FileMode.Create, FileAccess.ReadWrite);
  workbook.SaveAs(output, ExcelSaveType.SaveAsMacro);
  input.Close();
  output.Close();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  // Instantiate the excel application object.
  IApplication application = excelEngine.Excel;

  // Opening a workbook
  IWorkbook workbook = application.Workbooks.Open("Test.xls");
  workbook.Version = ExcelVersion.Excel2016;
  IWorksheet sheet = workbook.Worksheets[0];

  //Accessing Vba project
  IVbaProject project = workbook.VbaProject;

  //Accessing vba modules collection
  IVbaModules vbaModules = project.Modules;

  //Remove all macros
  vbaModules.Clear();

  //Saving as macro document
  workbook.SaveAs("Output.xlsm");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  'Instantiate the excel application object.
  Dim application As IApplication = excelEngine.Excel

  'Opening a Workbook
  Dim workbook As IWorkbook = application.Workbooks.Open("Test.xls")
  Dim sheet As IWorksheet = workbook.Worksheets(0)

  'Creating Vba project
  Dim project As IVbaProject = workbook.VbaProject

  'Accessing vba modules collection
  Dim vbaModules As IVbaModules = project.Modules

  //Remove all macros
  vbaModules.Clear()

  'Saving as macro document
  workbook.SaveAs("Output.xlsm")
End Using

A complete working example to remove all macros in C# is present on this GitHub page.

SkipOnSave

SkipOnSave allows to resave the Excel document into normal XLSX and XLS documents.

The following code illustrate how to save the macro-enabled document into normal Excel document.

C# [Cross-platform]
C# [Windows-specific]
VB.NET [Windows-specific]
using (ExcelEngine excelEngine = new ExcelEngine())
{
  //Instantiate the excel application object.
  IApplication application = excelEngine.Excel;

  //Opening form module existing workbook
  FileStream input = new FileStream("Test.xls", FileMode.Open, FileAccess.ReadWrite);
  IWorkbook workbook = application.Workbooks.Open(input);
  IWorksheet sheet = workbook.Worksheets[0];

  //Skip Macros while saving
  application.SkipOnSave = SkipExtRecords.Macros;                

  //Saving as Excel without macro 
  FileStream output = new FileStream("Output.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.SaveAs(output, ExcelSaveType.SaveAsXLS);
  input.Close();
  output.Close();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  // Instantiate the excel application object.
  IApplication application = excelEngine.Excel;

  // Opening a workbook
  IWorkbook workbook = application.Workbooks.Open("Test.xls");
  workbook.Version = ExcelVersion.Excel2016;
  IWorksheet sheet = workbook.Worksheets[0];

  //Skip Macros while saving
  application.SkipOnSave = SkipExtRecords.Macros;                

  //Saving as Excel without macro
  workbook.SaveAs("Output.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  'Instantiate the excel application object.
  Dim application As IApplication = excelEngine.Excel

  'Opening a Workbook
  Dim workbook As IWorkbook = application.Workbooks.Open("Test.xls")
  Dim sheet As IWorksheet = workbook.Worksheets(0)

  //Skip Macros while saving
  application.SkipOnSave = SkipExtRecords.Macros             

  'Saving as Excel without macro 
  workbook.SaveAs("Output.xlsx")
End Using

A complete working example to skip macro on save in C# is present on this GitHub page.