VBA vs Power Automate: which one should automate your Excel task?
Watch alongside this article
Public YouTube resources matched to this article's topic, functions, and practice goal.


Automate Excel Without Becoming a Programmer | Macros & VBA 101
MyOnlineTrainingHub
Open on YouTube (opens in a new tab)
Both automate Excel work and the feature lists overlap enough to be unhelpful. There is a single question that resolves most real cases:
What starts the work?
If a person starts it — someone has the workbook open and clicks something — that is VBA’s territory. If an event or a clock starts it — a file lands, an email arrives, it is Monday — that is Power Automate’s, because VBA cannot act on a workbook nobody has opened.
Almost everything else is detail.
Why “what starts it” decides it
VBA has no existence outside a running instance of Excel. A macro in a closed workbook on a switched-off laptop is inert. Getting VBA to run on a schedule means Windows Task Scheduler launching Excel on a logged-in machine that stays on — technically possible, routinely fragile, and the cause of the classic “the report didn’t run because Dave was on leave and his PC was off”.
Power Automate runs in the cloud on Microsoft’s infrastructure. It does not care whether anyone is logged in. That is the capability VBA structurally cannot match, and it is why “it needs to happen at 6am” ends the discussion on its own.
Running the other way: Power Automate cannot react to a user. There is no
equivalent of Worksheet_Change firing the moment someone types in B4, no way
to validate an entry before it is committed, no way to drive the UI of the
workbook in front of a person. Those are
workbook and worksheet events, and
they are VBA-only by construction.
What VBA is better at, concretely
- Reacting to the user in real time — validate on entry, protect a range conditionally, cascade a dropdown, fire on selection change.
- Fine-grained workbook control. Chart series formatting, pivot cache manipulation, shapes, custom number formats, print areas. Power Automate’s Excel connector essentially sees rows in a table; VBA sees everything.
- Speed. Operations are in-process. A loop that would be 5,000 cloud calls in a flow is instant here, especially written array-in/array-out as in common VBA snippets.
- No licence question, no admin, no connection. It runs on a laptop in a tunnel.
- Local files and other Office apps, through COM.
What Power Automate is better at, concretely
- Triggers that are not a person: a file created in SharePoint, an email with an attachment, a Forms response, a recurrence, a Teams message.
- Moving between systems. Outlook, SharePoint, Teams, Planner, Dataverse are first-party. VBA can be made to talk to these, painfully, with auth you maintain yourself.
- Running when the machine is off.
- Surviving the author leaving. A flow is visible in the tenant, owned by an
account, and can be transferred. A macro is buried in a workbook or in
somebody’s
PERSONAL.XLSBwhere literally no one else will ever find it. - Being allowed to exist. Many organisations block macros by default — files
from outside the network open with macros disabled, and some tenants prohibit
.xlsmoutright. A flow has none of that friction, and this is increasingly the practical decider regardless of technical merit.
The real limits, stated honestly
Power Automate’s Excel connector is shallower than people expect. It works on tables in a OneDrive/SharePoint-hosted file, row at a time, and each action is a network call — so a flow looping 2,000 rows is slow and burns run quota. Formatting, charts and pivots are mostly out of reach.
The escape hatch is Office Scripts: a TypeScript script that runs inside Excel online, called from a flow as a single action. The flow handles the trigger and the plumbing; the script does the in-workbook work in one hit. For anyone whose instinct is VBA, that is the modern shape of the answer, and it works in the browser where VBA does not run at all.
VBA’s limits are the mirror image: nothing happens unless somebody opens the file, distribution means emailing a new copy to everyone, and it is only as reliable as the one machine it runs on.
The pattern when it is both
The common production shape, worth copying:
- Power Automate watches a SharePoint folder, or a mailbox, for the file.
- It drops the file where it belongs and logs that it arrived.
- Power Query — not VBA — does the reshaping, because transformation is what it is for and it needs no code at all. See Power Query vs VBA for data cleanup.
- VBA or an Office Script does what neither of the above can: the formatting, the chart rebuild, the PDF export.
- The flow notifies somebody that it finished, and — importantly — that it failed, if it did.
That last step is the one teams skip. Both tools fail silently. A flow stops when a credential expires; a macro errors into a dialog on an unattended machine. Build an explicit “ran, N rows” message to a channel or inbox, so that silence is never the success state.
Deciding
| The automation… | Use |
|---|---|
| Fires when a user types or clicks | VBA |
| Fires on a schedule or an event | Power Automate |
| Needs charts, formatting, pivots | VBA / Office Script |
| Moves data between M365 apps | Power Automate |
| Must run with the laptop off | Power Automate |
| Must run offline, no licence | VBA |
| Must work in Excel for the web | Office Script |
| Is really a reshaping job | Power Query |
Start at the bottom row. A surprising proportion of tasks that arrive framed as “should I write a macro or a flow” are transformation work, and Power Query does them with no code, no licence and no trigger to go stale.
If you are picking up VBA for the first half of that table, the VBA macros course path sequences it, starting with recording your first macro.
FREE EXCEL UPDATES
Get practical Excel tips in your inbox
New tutorials, tools, templates, and worked examples a few times a month. Unsubscribe anytime.
Go deeper with this skill
Turn the idea into a repeatable Excel workflow you can explain, rebuild, and review later. For this article, the goal is to practice: The deciding question is not which is more powerful — it is what starts the work. If a person starts it, VBA. If an event or a clock starts it, Power Automate. Plus the pattern for when the answer is both.
Practice workbook setup
Create a small practice workbook with one raw-data sheet, one working sheet, and one final output sheet.
Practice workflow
- Rebuild the example once exactly as described, then repeat it with different labels, dates, or amounts.
- Write a short note beside the result explaining what each step is doing and why it matters.
- Change one input value and confirm the output updates in the way you expected.
- Save a clean copy of the workbook before experimenting further.
Quality checks
- Inputs, calculations, and final outputs are separated clearly.
- Headings describe the data without relying on memory or hidden context.
- The final result can be understood by someone who did not build the workbook.
Common mistakes
- Mixing raw data and manual adjustments in the same cells.
- Skipping a quick review after the result looks correct.
- Building a one-off fix instead of a repeatable workflow.
Next actions
- Apply the same pattern to a real workbook with 20 to 50 rows of sample data.
- Add one note that explains when you would not use this approach.
Formula focus: even if this workflow is not formula-heavy, add one check cell that confirms the final output still matches the source data.






