Python vs VBA for Excel Automation

VBA has automated Excel for thirty years and lives inside the workbook; Python arrived later and works mostly from the outside. They overlap, which is why the question comes up on every team that has both skills — but they are good at different things, and the right answer is often "both, for different parts of the job". This guide, part of Choosing a Python Excel Library, compares them on the dimensions that decide real projects and builds the same small report in each.

The same task in both languages

The task: a sheet of orders; highlight every order over 1,000 and write a total per region to a summary sheet. First in VBA, as a macro stored in the workbook:

Sub BuildSummary()
    Dim ws As Worksheet, sm As Worksheet, r As Long, last As Long
    Dim totals As Object: Set totals = CreateObject("Scripting.Dictionary")
    Set ws = ThisWorkbook.Sheets("Orders")
    Set sm = ThisWorkbook.Sheets("Summary")
    last = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    For r = 2 To last
        If ws.Cells(r, 3).Value > 1000 Then ws.Range(ws.Cells(r, 1), ws.Cells(r, 3)).Interior.Color = RGB(252, 233, 233)
        totals(ws.Cells(r, 2).Value) = totals(ws.Cells(r, 2).Value) + ws.Cells(r, 3).Value
    Next r
    sm.Range("A1:B1").Value = Array("Region", "Total")
    Dim k, i As Long: i = 2
    For Each k In totals.Keys
        sm.Cells(i, 1).Value = k: sm.Cells(i, 2).Value = totals(k): i = i + 1
    Next k
End Sub

Then in Python, working on the file without Excel — first some sample data so the example runs as written:

import numpy as np
import pandas as pd

rng = np.random.default_rng(3)
orders = pd.DataFrame({
    "order_id": np.arange(1001, 1101),
    "region": rng.choice(["North", "South", "East", "West"], 100),
    "amount": rng.integers(100, 2500, 100),
})
orders.to_excel("orders.xlsx", sheet_name="Orders", index=False)

from openpyxl import load_workbook
from openpyxl.formatting.rule import FormulaRule
from openpyxl.styles import PatternFill

data = pd.read_excel("orders.xlsx", sheet_name="Orders")
totals = data.groupby("region", as_index=False)["amount"].sum().rename(columns={"amount": "total"})

with pd.ExcelWriter("orders.xlsx", mode="a", engine="openpyxl", if_sheet_exists="replace") as writer:
    totals.to_excel(writer, sheet_name="Summary", index=False)
    ws = writer.book["Orders"]
    last = ws.max_row
    ws.conditional_formatting.add(
        f"A2:C{last}",
        FormulaRule(formula=["$C2>1000"], fill=PatternFill("solid", fgColor="FCE9E9")),
    )

print(pd.read_excel("orders.xlsx", sheet_name="Summary"))

The two are about the same length, and each shows its character. The VBA macro walks the cells one by one and paints fixed colours — natural inside Excel, where the cells are the data. The Python version treats the sheet as a table, aggregates it in one call, and adds a conditional formatting rule, so the highlighting follows the data if someone edits an amount. It also ran without Excel, which means it can run on a server at 6 a.m. The VBA runs only when someone opens the workbook in desktop Excel and clicks.

How they compare

A few rows deserve comment.

Interaction. If the requirement is "when the user changes a cell, recalculate this and colour that", or "a button that opens a form", VBA is the natural tool: it lives in the workbook and receives Excel's events. Python can be called from a button — see Run Python from an Excel Button with xlwings RunPython — but event handling itself stays in VBA.

Data work. Joining a CRM export to a price list, cleaning ten thousand addresses, aggregating a year of transactions: pandas does these in a few lines and at compiled speed. The same in VBA means loops, dictionaries and arrays, and it slows down sharply as data grows.

Security and delivery. Workbooks with macros must be .xlsm, and Microsoft blocks macros by default in files that arrive from the internet, so recipients see a security banner or nothing at all. A Python job that generates a plain .xlsx delivers a file with no code in it — nothing to enable, nothing to block.

Maintenance. VBA lives inside binary workbooks, which makes diffs, code review and automated tests awkward. Python code lives in text files with the whole ecosystem of linters, tests and version control — the practices in Testing and Packaging Excel Automation Scripts apply directly.

Choosing, and combining

The combinations are often better than either alone. A workbook keeps a small VBA macro for its buttons while the heavy lifting runs in Python; or an existing VBA-heavy workbook stays as it is while a Python job feeds it cleaned data every morning. When you do need to run an existing macro from Python — for example as one step in a scheduled job — see Run an Excel Macro from Python with win32com.

Where Office Scripts fit

Workbooks increasingly live in SharePoint and OneDrive and are opened in the browser, where VBA does not run at all. Excel for the web has its own automation language, Office Scripts: TypeScript scripts stored with the user's account rather than in the workbook, recordable with an action recorder, and able to run from a button in the workbook or from a Power Automate flow on a schedule. For a team whose files already live in Microsoft 365, they cover a good part of what small VBA macros used to do — formatting a sheet, copying a range, refreshing a table — without macro-enabled files.

They do not replace Python for data work. Office Scripts run with limits on execution time and on how much data a script can move per call, and they have no equivalent of pandas. The practical split in a Microsoft 365 setup is: Office Scripts for small in-workbook actions that users trigger, Python — running as a scheduled job and editing cloud workbooks through Microsoft Graph — for the heavy processing. The Graph side of that is covered in Automating Excel Online with Microsoft Graph. Python in Excel is a fourth option again: Python code inside cells, for analysis rather than automation, with the constraints described in Using Python in Excel.

Migrating a VBA macro to Python

Rewriting working VBA is only worth it when the macro has outgrown Excel: it processes more data every month, it needs to run without someone clicking, or several people now maintain it. Migrate in slices rather than all at once. Start with the data processing — the loops that read, filter and aggregate — and replace them with a Python step that writes its results to a sheet the macro reads. Leave the user-facing parts in VBA until there is a reason to move them. Check the result against the old macro on real files before switching over: run both on last month's input and compare the output workbooks cell by cell, as in Compare Two Excel Files for Differences with Python.

Common pitfalls

• Choosing by familiarity alone. A team fluent in VBA will write data pipelines in VBA that would be a tenth of the length in pandas; a Python team will fight Excel's object model for what a five-line macro does. Match the tool to the task.
• Driving Excel from Python cell by cell. Through COM each cell access is a cross-process call. If Python must drive Excel, read and write whole ranges at once — see Speed Up xlwings with Bulk Reads and Writes.
• Shipping macros to external recipients. Blocked macros are the norm now. Deliver plain .xlsx output and keep code on your side.
• Assuming Office Scripts is VBA. Excel for the web uses Office Scripts (TypeScript), not VBA. Neither VBA nor desktop-style Python automation runs there.

Conclusion

VBA belongs inside desktop Excel, reacting to users with buttons, events and forms. Python belongs around Excel: processing data with pandas, generating and delivering workbooks without Excel, running on schedules and servers, and living in version-controlled, testable code. Use VBA for interaction, Python for data and delivery, and connect them with a button or a scheduled job when a task needs both.

Frequently asked questions

Is Python replacing VBA in Excel? No. VBA is still supported in desktop Excel and remains the simplest way to automate the application's user interface from inside a workbook. Python is better for processing data, building reports outside Excel, and running unattended on servers. Many teams use both.

Can Python do everything VBA can? Not from outside Excel. Python libraries such as openpyxl write files without Excel, so they cannot respond to clicks, show dialogs or run when a cell changes. Driving the application with xlwings or COM from Python covers much of VBA's reach on Windows, but event-driven features still belong to VBA.

Why do my VBA macros not run in files downloaded from the internet? Microsoft blocks macros by default in Office files marked as coming from the internet. Users must unblock the file or it must come from a trusted location. Python scripts that generate plain .xlsx files avoid macros in the delivered workbook altogether.

Which is faster? For cell-by-cell work inside Excel, VBA is usually faster than Python driving Excel through COM, because each COM call crosses a process boundary. For processing data — joining, aggregating, cleaning — Python with pandas working on the data outside Excel is typically far faster than VBA loops over cells.

Related

• Up one level: Choosing a Python Excel Library — picking tools for Excel work.
• xlwings: Run a VBA Macro From Python — reusing existing macros.
• Work with Macro-Enabled .xlsm Files in openpyxl — keeping VBA intact.
• Python vs Power Query for Cleaning Excel Data — the other built-in alternative.