Excel Automation

Replacing VBA Macros: Automating Excel Without Them

VBA still works and Microsoft has not removed it, but it runs on one person's desktop, breaks on updates and is usually undocumented. The modern replacements are Power Query for data shaping, Office Scripts for browser-based automation, Power Automate for cross-system workflows, and Python when the logic is genuinely complex.

8 min readUpdated 2026-09-15

When the macro is load-bearing and nobody can safely change it, Datanova helps businesses rebuild fragile macros as maintainable automation.

What actually goes wrong with VBA

VBA is not bad technology. A macro that works and that someone understands is fine, and replacing it for its own sake is waste. The failures are specific and they compound over years:

It runs on a desktop — if that machine is off, nothing happens
It usually has no version control, so there is one copy and no history
Macro-enabled files are increasingly blocked by security policy, especially from email or the internet
It breaks on Office updates, on 64-bit transitions, and on Excel for Mac
The author has often left, and nobody can safely change it
Errors fail silently or show a dialog nobody is there to see

The replacements, and what each is for

Tool	Best for	Watch out for
Power Query	Importing, cleaning, reshaping and combining data — most of what data-prep macros do	Transformation only; it does not orchestrate or send anything
Office Scripts	In-workbook automation that needs to run in Excel on the web, sharable across a team	Excel on the web and Microsoft 365; narrower reach than VBA had
Power Automate	Cross-system workflows — triggers, approvals, notifications, connectors	Per-user and premium-connector licensing adds up as usage grows
Python	Complex logic, heavy volumes, real integrations, anything needing tests	Needs somewhere to run, and someone who writes Python
Keep the VBA	It works, it is understood, and it is not blocked	Document it and put the file in version control anyway

Most macro replacements end up as Power Query for the data work plus one of the others for the orchestration. Reaching straight for Python is often over-engineering.

How to choose

Write down what the macro actually does, step by step. Most macros turn out to be one of: import and clean, format a report, send something, or move data between systems.
If it is import and clean, it is Power Query, and the rebuild is often hours rather than days.
If it formats or manipulates the workbook and the team is on Microsoft 365, Office Scripts is the closest replacement.
If it crosses systems — email, SharePoint, an ERP, an approval — Power Automate, unless the licensing arithmetic argues otherwise.
If the logic is genuinely complex, the volumes are large, or it needs to be tested, Python running on a server.
If it works, is understood and nothing blocks it, keep it. Document it, put it in version control, and move on.

Rebuilding without breaking the process

Capture the current outputs first — run the macro and save exactly what it produces. That is your test case.
Rebuild against those outputs and compare them field by field, not by eye.
Expect undocumented behaviour. Macros accumulate special cases, and the exceptions are the part nobody remembers.
Run both for a few cycles before turning the old one off.
Put the replacement somewhere that is not one person's desktop, or you have rebuilt the same problem in a new language.

That last point is the one that matters most. Moving a macro to Python and then running it manually from the same laptop every Monday has changed the language and none of the risk.

Frequently asked questions

Is VBA deprecated?

No. Microsoft still supports VBA in desktop Excel and has not announced its removal. What has changed is the surrounding environment: macro-enabled files are blocked by default in more situations, VBA does not run in Excel on the web, and Microsoft's investment is clearly going into Power Query, Office Scripts and Power Automate.

How do I automate Excel without VBA?

Power Query for importing, cleaning and combining data; Office Scripts for automation inside the workbook that needs to run on the web; Power Automate for workflows crossing systems; and Python for complex logic or large volumes. Most real replacements combine Power Query with one of the others.

Can Power Query replace my macros?

It can replace the ones that import, clean, reshape or combine data, which in practice is a large share of business macros. It cannot replace macros that format the workbook, interact with the user, or send things — those need Office Scripts, Power Automate, or code.

Should we rewrite working macros?

Not for their own sake. Rewrite when the macro is blocked by policy, breaks on updates, nobody understands it, or the process cannot tolerate depending on one person's machine. A working, documented macro that someone can maintain is not a problem worth spending money on.

Depending on a macro nobody understands?

In a free consultation we look at what the macro actually does and tell you the cheapest way to make the process survive without it — including when the answer is to document it and keep it.

Book a Free Consultation