How to Add & Edit Excel VBA Macros in Python (Without Excel Installed)

Need to modify VBA macros in Linux or cloud environments? Bypass openpyxl limitations and eliminate Windows-only win32com dependencies. Here is how to add, edit, and remove Excel VBA macro code directly in Python — no Excel runtime required.

Add and Edit Excel VBA Macros in Python

Python makes most Excel automation tasks straightforward, but working with VBA code embedded in a workbook can be tricky, especially when Excel is not available. In this article, we’ll show how to manage VBA macros in Excel using Python, including how to add a macro to a workbook, modify existing VBA code, and remove VBA modules that are no longer needed — all without opening or automating Microsoft Excel.

• Prerequisites & Setup
• Understanding VBA Module Types
• Add a VBA Macro to an Excel File with Python
• Modify Existing VBA Code in Excel with Python
• Remove VBA Macros from Excel in Python
• Conclusion

Prerequisites & Setup

To manipulate Excel VBA code programmatically in Python without calling the Office COM interface, we need a library that can read and parse the binary structure of macro-enabled files (.xlsm or .xlsb).

In this article, we’ll use Spire.XLS for Python. It works as a standalone engine, making it suitable for cross-platform deployments like Linux containers or cloud environments.

You can install it via PyPI:

pip install Spire.XLS

Note: This library manipulates the source code within the file; it does not execute VBA macros. Running the macros still requires an environment that supports VBA, such as Microsoft Excel.

Understanding VBA Module Types

When adding VBA code programmatically, you need to choose the correct module type. In Excel, different module types are intended for different kinds of VBA logic.

• Standard Module
  A general-purpose container for VBA code. Commonly used for regular macros, reusable Sub procedures, and custom functions (UDFs) that are not tied to a specific workbook object.
• Document Module
  These modules are directly attached to Excel objects like ThisWorkbook or individual sheets. They are used for event-driven code, such as executing a macro automatically when a workbook opens or a worksheet changes.
• Class Module
  A module used to define custom VBA classes. It lets you create reusable objects with their own properties, methods, and events.
• UserForm
  A module associated with a custom visual user interface. It contains both the graphical design of dialog boxes and the code that handles control events like button clicks or dropdown selections.

For most general-purpose macros, a standard module is the correct choice. Code that responds to workbook or worksheet events should instead be placed in the corresponding document module.

Add a VBA Macro to an Excel File with Python

Adding a VBA macro to an Excel workbook requires three main steps:

1. Add a new module to the workbook’s Modules collection and specify its type via the VbaModuleType enum.
2. Assign your VBA source code as a multi-line string to the module’s SourceCode property.
3. Save the workbook in a macro-enabled format.

The following example creates a standard VBA module, populates it with a macro that fills the active worksheet with sample project data, and applies basic formatting:

from spire.xls import *

# Create a new workbook
workbook = Workbook()

# Access the VBA project
vba_project = workbook.VbaProject
vba_project.Name = "SampleVBAMacro"

# Add a standard VBA module
vba_module = vba_project.Modules.Add("SampleModule", VbaModuleType.Module)

# Add VBA code to the module
vba_module.SourceCode = """
Sub ExampleMacro()

    Dim ws As Worksheet
    Dim i As Integer

    ' Get the active worksheet
    Set ws = ActiveSheet

    ' Clear existing content
    ws.Cells.Clear

    With ws
        ' Add headers
        .Range("A1:C1").Value = Array("Serial No.", "Project Name", "Amount")

        ' Add sample data
        For i = 1 To 10
            .Cells(i + 1, 1).Value = i
            .Cells(i + 1, 2).Value = "Project " & i
            .Cells(i + 1, 3).Value = i * 100
        Next i

        ' Auto-fit columns
        .Columns("A:C").AutoFit

        ' Format the header row
        With .Range("A1:C1")
            .Font.Bold = True
            .Interior.Color = RGB(200, 220, 255)
        End With

        ' Format the amount column
        .Range("C2:C11").NumberFormat = "$#,##0.00"
    End With

    MsgBox "Data population complete!", vbInformation, "Operation Prompt"

End Sub
"""

# Save as a macro-enabled workbook
workbook.SaveToFile("AddVBAMacro.xlsm", FileFormat.Xlsm)
workbook.Dispose()

Here, we save the workbook as an .xlsm file. If you save it with a regular .xlsx extension, VBA code will not be preserved. You can also save the file to other Macro-enabled Excel formats, such as:

• .xlsb — Binary Excel workbook
• .xls — Legacy Excel workbook
• .xltm — Macro-enabled Excel template

For most modern workbooks containing macros, .xlsm remains the standard and most popular choice.

Modify Existing VBA Code in Excel with Python

To update an existing macro, you need to access the workbook’s VBA project, find the target module, and modify its SourceCode property. Since the code is returned as a plain Python string, you can either replace specific parts of it or overwrite the entire string.

Get Alice Yang’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

The following example loads the .xlsm workbook from the previous step, targets the standard module named SampleModule, and uses Python’s string .replace() method to update the calculation formula and the completion pop-up message:

from spire.xls import *

# Load the workbook created in the previous example
workbook = Workbook()
workbook.LoadFromFile("AddVBAMacro.xlsm")

# Access the VBA project
vba_project = workbook.VbaProject

# Get the standard module by name
vba_module = vba_project.Modules["SampleModule"]

# Get the existing VBA source code
source_code = vba_module.SourceCode

# Update part of the macro
source_code = source_code.replace("i * 100", "i * 150")

source_code = source_code.replace("Data population complete!", "Data population updated!")

# Write the updated code back to the module
vba_module.SourceCode = source_code

# Save the updated workbook
workbook.SaveToFile("ModifyVBAMacro.xlsm", FileFormat.Xlsm)
workbook.Dispose()

Accessing Document Module

The method above works for standard modules. If the VBA code you want to modify belongs to a document module associated with a worksheet (such as Worksheet_Change or Worksheet_Activate events), you need to get the worksheet object first and pass it to GetWorksheetModule():

# Access the module for the first worksheet
worksheet = workbook.Worksheets[0]
vba_module = vba_project.Modules.GetWorksheetModule(worksheet)

Remove VBA Macros from Excel in Python

There are several ways to remove macros from Excel, depending on whether you want to delete a particular VBA module or strip all macros from the workbook entirely.

Remove a Specific VBA Module

If you only need to get rid of a particular macro module, you can remove it from the Modules collection by its name. This leaves the rest of the VBA project untouched.

from spire.xls import *

workbook = Workbook()
workbook.LoadFromFile("Sample.xlsm")

vba_project = workbook.VbaProject

# Remove a specific VBA module
vba_project.Modules.Remove("SampleModule")

workbook.SaveToFile("RemoveModule.xlsm", FileFormat.Xlsm)
workbook.Dispose()

Clear All Macros (Keep .xlsm Format)

If you want to wipe all VBA code but your system requires the output file to remain in the .xlsm format, you can set the workbook’s HasMacros property to False.

workbook = Workbook()
workbook.LoadFromFile("Sample.xlsm")

# Remove macros from the workbook
workbook.HasMacros = False

workbook.SaveToFile("RemoveAllMacros.xlsm", FileFormat.Xlsm)
workbook.Dispose()

Convert to a Macro-Free Workbook (.xlsx)

If you want to ensure the file is completely clean for regular users, you can save it as a standard .xlsx file. Because .xlsx does not support binary VBA components, the macros are automatically dropped during the save process.

workbook = Workbook()
workbook.LoadFromFile("Sample.xlsm")

# Save as an .xlsx file to drop the macros
workbook.SaveToFile("MacroFreeWorkbook.xlsx", FileFormat.Version2016)
workbook.Dispose()

Conclusion

This article showed how to interact with the underlying VBA modules in an Excel file using Python. Whether you need to insert macros, update existing VBA code, or clean up macros before delivering them to users, you can now handle the entire process programmatically without needing Microsoft Excel installed.

Related Topics

• 5 Openpyxl Alternatives for Excel Automation in Python
• Working with Excel Formulas in Python
• Convert Rows to Columns and Vice Versa in Excel with Python
• Convert Excel XLS or XLSX to JSON in Python