This quick guide explains the process to add code in Excel VBA library using Python. It has details to set the IDE for development, a list of steps defining the programming tasks, and a sample code demonstrating how to Excel macro library using Python. You will learn to access the VBA library and add modules/codes as per the requirements.
Steps to Add Excel VBA Source Code Library using Python
- Set the IDE to use Aspose.Cells for Python via Java to update the VBA library
- Create a Workbook object, access a sheet and add a new module
- Access the newly created module using the returned index while creating the module
- Set the name of the module
- Set code for the module
- Save the resultant Excel file as XLSM
These steps summarize the process to access VBA code library using Python and adding modules. Commence the process by creating or loading a workbook, selecting a worksheet, and adding a module and code to the VBA project. Finally, set the name and codes in the newly created module and save the XLSM file on the disk.
Code to Update VBA Code Library using Python
| import jpype | |
| import asposecells as cells | |
| jpype.startJVM() | |
| from asposecells.api import License, Workbook, SaveFormat | |
| # Instantiate a license | |
| license = License() | |
| license.setLicense("License.lic") | |
| # Create a workbook | |
| wb = Workbook() | |
| # Select a sheet | |
| ws = wb.getWorksheets().get(0) | |
| # Add VBA Module and get its reference | |
| idx = wb.getVbaProject().getModules().add(ws) | |
| module = wb.getVbaProject().getModules().get(idx) | |
| # Assign a name to the module | |
| module.setName("SetGreenEven") | |
| # Set code for the module | |
| module.setCodes(""" | |
| Private Sub Worksheet_Change(ByVal Target As Range) | |
| Dim ws As Worksheet | |
| Dim cell As Range | |
| ' Set the active sheet | |
| Set ws = ActiveSheet | |
| ' Check if the changed cells have even numeric values | |
| For Each cell In Target | |
| If IsNumeric(cell.Value) And cell.Value Mod 2 = 0 Then | |
| ' Set the background color to green | |
| cell.Interior.Color = RGB(0, 255, 0) ' RGB for green | |
| Else | |
| ' If the value is not even, reset the background color to default (optional) | |
| cell.Interior.ColorIndex = xlNone | |
| End If | |
| Next cell | |
| End Sub | |
| """) | |
| # Save the output | |
| wb.save("sampleWithMacro.xlsm", SaveFormat.XLSM) | |
| print("VBA Module with code added successfully!!!") |
This sample code demonstrates the process to add a module with code in the Excel VBA code library using Python. This sample VBA code changes the background color of each cell to green when you enter an even number in the cell. You may set the Vba module type to PROCEDURAL, DOCUMENT, CLASS or DESIGNER.
This article has covered the process of adding macro to an Excel file. If you want to unprotect an Excel file, refer to the article on How to unprotect Excel in Python.
