VBA Versus Python for Excel Automation in Real Projects
Compare VBA and Python for automating Excel by where the code runs, what it can touch, how it is tested and who maintains it, with a rule for choosing.
Both languages can automate Excel, and both are good at it in different places. The argument between them tends to be about taste, and the useful question is smaller: where does this job run, who touches it and what must it connect to? This post compares VBA and Python on the points that decide real projects, gives a rule for choosing, and covers the combined setup that many teams end up with. It is a spoke of automating Excel work without clicking.
What each one is
VBA (Visual Basic for Applications) is the macro language built into Excel and the other Office applications. The code lives in the workbook (or an add-in), runs inside the Excel process and drives the application's object model: workbooks, sheets, ranges, pivot tables, charts, the ribbon, dialogs. A macro can be attached to a button, a keyboard shortcut or an event such as opening the file.
Python is a general-purpose language. Used with Excel, it works in one of three ways: libraries that read and write the files directly without Excel (openpyxl, XlsxWriter, pandas), a bridge that controls a running Excel (xlwings, or pywin32 over COM), or Microsoft's own Python in Excel feature, which runs formulas in a Microsoft-hosted environment. The first way is the one that matters for automation, because it needs no Excel at all.
The difference follows from that. VBA is Excel from the inside. Python is a program that happens to produce and consume workbooks.
Where the code runs
VBA needs a running copy of desktop Excel. It does not work in Excel for the web, and not on a server without Office installed. Microsoft advises against running desktop Office unattended on servers, because dialogs and sessions behave differently with no user present.
Python with openpyxl or pandas needs only Python. It runs on a Linux server, in a container, on a schedule, with no display. That alone settles many choices: if the job runs at six in the morning on a machine nobody logs into, use Python (automating weekly Excel reports with Python and cron).
The bridge approaches, xlwings and COM, combine both: Python scripts that control a real Excel. They need Excel installed and inherit its limits, and they are right for the narrow case where you must call an Excel feature that no library implements, such as refreshing a Power Query or manipulating a PivotTable.
What each can touch
VBA reaches everything in Excel's object model: PivotTable fields, slicers, charts with full formatting, conditional formats, data validation, form controls, the ribbon, the clipboard, other Office applications. If the task is "when the user clicks this button, refresh the data and format the chart", VBA is the natural tool.
Python libraries cover the file format, which is large but not everything. openpyxl and XlsxWriter write cells, styles, charts, tables, validation and conditional formats. They do not run Power Query, do not calculate formulas, and create pivot tables only in limited ways. pandas reads and writes tables well and formatting poorly. For data work, Python's strength is outside Excel: it reads databases, APIs, CSV, JSON and Parquet, cleans data with tested code and writes a finished workbook.
Testing and maintenance
This is where the languages part ways most. A Python script is a text file in a repository. It can be reviewed, diffed, tested with pytest, run in continuous integration and rolled back. A function that cleans a column has a unit test with a small input and an expected output.
VBA lives inside a binary workbook. Version control sees an opaque file. Review means opening the editor. Testing is possible with frameworks, but rare in practice. Code copied between workbooks diverges. The macro for the monthly report is in the copy of the workbook that Maria keeps on her desktop, and nobody knows whether it is the latest. These are problems of habit more than of the language, and the habit is hard to break because the code and the data live in one file.
If the logic is nontrivial, and a mistake costs money, the testability of Python is a strong reason to prefer it. If the logic is thirty lines that rearrange a sheet, the risk is small and VBA is fine.
Distribution and security
A macro-enabled workbook (.xlsm) is blocked by default when it arrives from the internet or from email. Microsoft changed Office in 2022 so that macros in files with the Mark of the Web do not run, unless the user takes steps to unblock the file. Organizations often disable macros altogether through policy. So VBA that works on your desk may not run for the colleague you send it to. Signing the macros with a certificate and using trusted locations helps and adds administration.
Python scripts run outside the document, under your own account and your own controls. A script is less likely to be blocked by Office policy, and more likely to be questioned by IT if it needs a runtime installed. In locked-down environments neither is free. For data teams the usual position is that macros are an attack surface, and scripts in a repository are reviewable (macros and why data teams should block them).
Performance
Both are fast enough for ordinary work. VBA is slow when it touches cells one at a time, because each read or write crosses a process boundary. The standard fix is to read a range into an array, work in memory and write it back once, and to turn off screen updating and automatic calculation during the macro. Python with openpyxl is slow on very large sheets for a different reason, the cost of parsing XML, and has write-only and read-only modes to help (reading large Excel files with read only mode). For big data, pandas and DuckDB do the heavy work and Excel receives a summary.
A rule for choosing
Put the questions in order.
Does a person drive it inside an open workbook? Buttons, forms, event handlers, formatting that responds to clicks: VBA.
Does it run with nobody there? A schedule, a server, a pipeline: Python.
Does it read from databases, APIs or many files? Python.
Does it need an Excel-only feature, like refreshing Power Query or driving a PivotTable? VBA, or Python with xlwings.
Must it be tested, reviewed and versioned? Python.
Will it run on machines where macros are blocked? Python, or an add-in distributed by IT.
Is it a small change to a sheet that one person uses? VBA or even just a formula.
Most data-heavy projects come out as Python, and most interactive tools come out as VBA. The answer is not a loyalty; it is a reading of the job.
Using both
A common split puts each language where it is strong. Python extracts, cleans and writes the data into a workbook or a set of CSV files on a schedule. A workbook with a few macros then lets a person open it, refresh a chart and print. The boundary between them is a plain file in a known place, not a function call across a process. That keeps either side replaceable.
An example of the boundary: Python writes sales_2025_week40.xlsx to a shared folder with a fixed sheet layout. The VBA in a separate dashboard workbook opens the newest file in the folder, copies the sheet into its data area and refreshes the charts. If the VBA breaks, the data is still there. If the Python changes the layout, the check at the start of the macro (the sheet exists, the headers match) fails with a message.
Moving from VBA to Python
If you inherit a VBA tool and want to retire it, do it in slices. Document what the macro does by reading it and running it on a copy with sample data. Rewrite the data steps in Python and compare the outputs row by row against the macro's. Keep the VBA for interface parts until a replacement exists. Expect surprises: macros often encode business rules that nobody remembers, such as a skipped region or a rounding order. The comparison of outputs is where they surface.
The reverse, moving a Python script into VBA, is usually a sign the job needs a user interface. Think twice, since a small web form or a command-line tool may serve better than a macro.
When neither VBA nor Python is the right choice
A formula or Power Query would do. If the task is a lookup or a recurring clean-up, native features are simpler to maintain than any script (Power Query for repeatable Excel cleanups).
The work belongs in a database. If the macro exists to join and summarize tables, SQL does it better, and Excel reads the result.
The job is a one-off. Writing and testing automation for a task you will do once costs more than doing it by hand.
The process is the problem. If a person copies data between three workbooks every week, the fix is a shared source and a connection, not a script that copies faster.
Microsoft documents the object model in its VBA reference for Excel, and the openpyxl documentation describes the file approach. To convert a small result to JSON, use the CSV to JSON converter.
