VBA (Visual Basic for Applications) is a programming language that is used in MS Excel files in order to automate spreadsheets related operations. VBA macros are used to write user-defined functions that let you speed up the tasks you have to perform manually. In this article, you will learn how to work with VBA macros in MS Excel files programmatically. By the end of this article, you will be able to extract, add, and modify VBA macros in Excel workbooks using C#.
- Work with VBA Macros using C# API
- Extract VBA Macros from an Excel Workbook
- Add VBA Macros to an Excel Workbook
- Modify VBA Macro in an Excel Workbook
- Get a Free License
Work with VBA Macros in Excel Files - C# API
Aspose.Cells for .NET is a C# class library that is designed to automate spreadsheet manipulation from within the .NET applications. The API provides a wide range of features that empower you to create, modify, and convert MS Excel workbooks. In addition, the API makes it possible to work with VBA macros seamlessly. Aspose.Cells for .NET is available for download as a DLL file as well as hosted on NuGet.
PM> Install-Package Aspose.Cells
Extract VBA Macros from an Excel File using C#
Let’s start by extracting the VBA macros from an existing Excel workbook. The following are the steps to perform this operation.
- Create an object of the Workbook class to load the Excel file.
- Access each VBA module from Workbook.VbaProject.Modules collection into VbaModule object.
- Retrieve code from each module using VbaModule.Codes property.
The following code sample shows how to extract VBA macro from an Excel workbook using C#.
Add VBA Macros to an Excel File using C#
Aspose.Cells for .NET also allows you to add VBA macros to existing or new Excel files. This can be done by following the below steps.
- Create a new Excel file or load an existing one using Workbook class.
- Add a new worksheet or get the existing one using Worksheet class.
- Add a new VBA module using Workbook.VbaProject.Modules.Add(Worksheet) method and get the module’s ID.
- Access the VBA module from Workbook.VbaProject.Modules[idx] collection into VbaModule object.
- Add the name of the module using VbaModule.Name property.
- Add code to the module using VbaModule.Codes property.
- Save the workbook using Workbook.Save(String, SaveFormat.Xlsm) method.
The following code sample shows how to add a VBA macro to Excel workbook using C#.
Modify VBA Macro in an Excel File using C#
You can also modify the existing VBA macros in an Excel file using the following steps.
- Use Workbook class to load the Excel file.
- Access the VBA modules from Workbook.VbaProject.Modules collection into VbaModule object.
- Retrieve code from the desired module using VbaModule.Codes property.
- Replace the code and save the Excel file using Workbook.Save(String) method.
The following code sample shows how to modify a VBA macro in an Excel file using C#.
Get a Free License
You can use or evaluate Aspose.Cells for .NET for free using a temporary license valid for 30 days. Get yours now.
Conclusion
VBA macros in MS Excel files are used to automate various types of tasks related to spreadsheets. In order to handle VBA macros programmatically, this article covered how to extract, add, and modify code in VBA modules using C#. You can explore more about the C# spreadsheet manipulation API using documentation.
See Also
Tip: You may want to check out Aspose FREE macro removal web app.