Code for Excel: VBA, Office Scripts, or Python?

“Code for Excel” usually means one of three genuinely different things, and an AI tool will write any of the three without ever asking which one you meant. VBA runs on the desktop when someone or something triggers it. Office Scripts run in Excel on the web and, unlike VBA, can be called by Power Automate with nobody at the keyboard at all. Python in Excel runs inside a single cell to actually analyse data, not to automate clicks. Getting the wrong one is the single most common reason a request for “code for this” comes back technically working and practically useless.

Three kinds of code, three different jobs

• —VBA — the oldest option, desktop-only, and still the most capable for a complex multi-step action, a custom form, or anything that needs to react to something happening in the workbook itself.
• —Office Scripts — written in TypeScript, run in Excel on the web or desktop, and callable from Power Automate on a schedule or a trigger like a new file landing in a folder. VBA has no equivalent of this: every VBA run needs a person or a macro button to set it off.
• —Python in Excel — a formula, entered as =PY(...), that runs against a range with pandas-style syntax and returns a table, a statistic, or a plot back into the grid. It is for analysis, not for automating a sequence of actions.

Picking before you ask an AI tool for it

Describe a task as “automate this in Excel” with nothing else and an AI tool will default to VBA, because it is the most common pattern behind that exact phrasing. That default is a real problem the moment the actual need is something running unattended overnight — VBA has no scheduling mechanism at all, so a perfectly correct VBA macro simply cannot do that job, no matter how well it is written. Naming the constraint — “this needs to run without anyone opening the file” or “this needs to work in a browser, not just on my desktop” — before asking for code is what actually decides which of the three you get, and it is worth deciding yourself rather than letting the model guess from a vague request.

A worked example: a script nobody has to run by hand

Task: every weekday morning, take the rows in a table called NewData and append them to a Master table on a Tracking sheet — with nobody opening the workbook to do it.

A specific prompt: “Write an Office Script for Excel that reads every row from a table named NewData in the active workbook and appends those rows to a table named Master on a worksheet called Tracking. The function should accept the workbook parameter that Power Automate provides, need no user interaction, and do nothing if NewData is empty.”

A reasonable script back from that prompt reads roughly: function main(workbook: ExcelScript.Workbook) { const source = workbook.getTable("NewData"); const rows = source.getRangeBetweenHeaderAndTotal().getValues(); if (rows.length === 0) return; const target = workbook.getTable("Master"); target.addRows(-1, rows); }. Set as the action inside a Power Automate flow triggered on a 7am weekday schedule, that script runs with nobody at a keyboard and nobody opening Excel — the whole point of choosing this over a VBA macro someone would otherwise have to remember to run.

Where each one goes quietly wrong

• —VBA written and tested on your machine can fail silently on a colleague’s, because their macro security settings block it by default and nothing in the workbook explains why nothing happened — macro generator for Excel covers this failure mode and the checks for it in full.
• —An Office Script that runs unattended has no one watching it fail. A table name that does not match, or a schedule that silently stopped firing weeks ago, sits quietly in the Power Automate run history until someone happens to check it — which, because nothing is visibly broken, can be a long time.
• —Python in Excel runs the calculation in Microsoft’s cloud, not on your desktop — a real difference from every other formula in the sheet, and worth knowing before pointing it at anything sensitive, whatever your organisation’s policy on that turns out to say.

None of these show up as an error message, which is exactly the shape documented hallucination behaviour in language models predicts: fluent, working-looking output carries no guarantee the thing underneath is actually correct, and code that compiles clean is not the same claim as code that does the right thing on your actual data.

Code that runs without anyone watching still needs someone who checks it — on a schedule, not only when something looks wrong.

Checks before you trust it

1. 01For an Office Script wired to Power Automate: after the first several scheduled runs, open the flow’s run history and read it — do not assume success just because nothing complained. Silence is not the same evidence as a checked result.
2. 02For VBA: test it on a second machine with default macro-security settings before assuming it works wherever it needs to, the same routine covered for an AI-generated macro.
3. 03For Python in Excel: before running it against real data, confirm what actually leaves your desktop for the calculation, and whether that is fine for the data in question.
4. 04Whatever the language, read the generated code against your workbook’s real sheet, table and column names line by line — how to check an AI answer when you are not the expert is the general version of this habit, and generated code is exactly the kind of output that reads as finished before it has actually been checked.

What to do Monday

Name which of the three jobs the task actually is before asking for a line of code: something a person will trigger when needed, something that has to run with nobody watching, or something that computes a result from data already on the sheet. Writing a prompt that works on the first try covers the same discipline generally — a specific, constrained request beats a vague one every time — and OpenAI and Anthropic give the same guidance in their own prompting documentation. If the task is really about turning numbers already in the sheet into totals and groups, PivotTables solve that without any code at all, and if the same calculation needs to be reused across many cells rather than run as one action, a user-defined function is the better fit than a script.

This same judgement — deciding whether a task should even run unattended, and what to check if it does — is the general question behind data automation tools and the four-question test for what is worth automating at all; a once-off task rarely justifies the setup, however satisfying the automation would feel. Deciding in advance how you would notice an unattended script quietly failing, rather than after it has been silently wrong for a month, is the same principle the NIST AI Risk Management Framework sets out for any automated system. Getting good at naming exactly which tool a task needs, and what to check afterward, is the same skill PwC has measured a growing wage premium for — Coursium teaches it directly, one short lesson at a time. Stay ahead of AI by learning the tools on your phone.