This article guides on how to modify Excel VBA library using C#. It shares the IDE settings, a list of steps and a code snippet to add module in an Excel VBA code library using C#. It demonstrates accessing the VBA project and adding modules and code in the workbook.
Steps to Modify Excel VBA Code Library using C#
- Set the IDE to use Aspose.Cells for .NET to add module and code
- Create a workbook, access a sheet, and add a VBA module
- Access the newly created module using the new module index
- Set the name and code for the module
- Save the output Excel file with the new VBA macro
These steps explain the process to add module in Excel VBA source code library using C#. Access the VbaProject.Modules collection of the selected workbook and call the Add() method to add a new module. Get a reference to the newly created module and set its name and Codes to the desired values before saving the resultant XLSM file.
Code to Update Excel Macro Library using C#
| using System; | |
| using Aspose.Cells; | |
| using Aspose.Cells.Vba; | |
| class Program | |
| { | |
| static void Main(string[] args) // Add VBA code in Excel using C# | |
| { | |
| new License().SetLicense("License.lic"); | |
| // Create new workbook, access a sheet and add a VBA module | |
| Workbook workbook = new Workbook(); | |
| Worksheet worksheet = workbook.Worksheets[0]; | |
| int moduleIndex = workbook.VbaProject.Modules.Add(worksheet); | |
| // Access the VBA Module | |
| VbaModule module = workbook.VbaProject.Modules[moduleIndex]; | |
| // Set module name | |
| module.Name = "TestModule"; | |
| // Set module code | |
| module.Codes = | |
| @"Private Sub Worksheet_Change(ByVal Target As Range) | |
| ' Check if the changed cell is A1 | |
| If Target.Address = ""$A$1"" Then | |
| ' Check if the changed cell is not empty | |
| If Target.Value <> """" Then | |
| ' Display a message box | |
| MsgBox ""Text entered in cell A1: "" & Target.Value, vbInformation, ""Cell A1 Change"" | |
| End If | |
| End If | |
| End Sub | |
| "; | |
| // Save the workbook | |
| workbook.Save("output.xlsm", SaveFormat.Xlsm); | |
| Console.WriteLine("VBA code added successfully"); | |
| } | |
| } |
This sample code demonstrates how to access VBA code library using C# in an Excel file and add module and code. The sample code in the module displays a message box in MS Excel whenever the cell A1 value is changed. This message box contains the updated value in the cell, however, you may change the code according to your requirements.
This article has taught us to add macros to an Excel file. To show formulas in Excel, refer to the article on how to show formulas in Excel using C#.
