Skip to content
NonStopExcel
← Articles

VBA vs Power Query vs Office Scripts: Which Should You Use to Automate Excel Reports?

8 min read

When a team decides to stop building a monthly report by hand, the next question is which tool to automate it with. Search for Excel VBA vs Power Query and you’ll find strong opinions in both directions, plus newer options like Office Scripts and Python that make the choice more confusing. They do different jobs, though. Most reports I automate use two of them together, each for the part it handles best.

This guide explains what each tool is for, which one to pick for common reporting tasks, and where VBA stands in 2026.

The four options in one paragraph each

Power Query is Excel’s data import and transformation engine, under Data > Get Data. It connects to files, folders, databases and SharePoint, cleans the data with recorded steps and loads the result into a table or the Data Model. You refresh it with one click. It does not format reports, send emails or click buttons for you.

VBA (Visual Basic for Applications) is the macro language built into desktop Excel. It can do nearly anything a user can do in Excel and a lot more outside it: create files, save PDFs, drive Outlook, loop through folders. It runs on Excel for Windows and Mac, but not in Excel for the web or on iPhone and iPad.

Office Scripts are the newer automation option, found on the Automate tab. Scripts are written in TypeScript (or recorded with the Action Recorder), stored in OneDrive or SharePoint, and run in Excel for the web, Windows and Mac. Their big advantage is Power Automate: a flow can run a script on a schedule or when a file arrives, without anyone opening Excel.

Python in Excel lets you write Python in a cell with the PY function. The code runs in the Microsoft Cloud on data you pass in from the grid, and it’s built for analysis: statistics, forecasting, charts with libraries such as pandas and matplotlib. It doesn’t automate Excel itself.

Which to use, by task

Task Best fit Why
Import exports from a system every month Power Query Recorded steps, one-click refresh, no code to maintain
Combine many files from a folder or SharePoint Power Query Built-in folder and SharePoint folder connectors
Clean data: split columns, fix dates, remove duplicates Power Query Repeatable steps that run on every refresh
Format a report, hide rows, set print areas VBA or Office Scripts Both control the workbook’s layout
Save the report as PDF and email it from Outlook VBA Works with Outlook and the file system on the desktop
Run on a schedule with nobody at the computer Office Scripts + Power Automate Cloud flows can trigger scripts
React to a cell change or button click VBA Office Scripts don’t support Excel events
Workbooks used in the browser or by a shared team Office Scripts VBA doesn’t run in Excel for the web
Statistical analysis, forecasting, advanced charts Python in Excel Access to Python’s analysis libraries

If you only learn one, learn Power Query. In reporting work, most of the hours go into getting data in and cleaning it, and that is exactly what it does. I wrote a separate Power Query guide for Excel if you’re starting from scratch.

Excel Automate tab vs macros

People often ask about the difference between the Automate tab and macros. They are two separate systems.

The Developer tab and View > Macros belong to VBA. Macros live inside the workbook, which then has to be saved as .xlsm, and they run with the same access to your computer as Excel itself.

The Automate tab belongs to Office Scripts. Scripts are saved as separate files in your OneDrive and can be shared with a workbook. Microsoft’s comparison of the two says Office Scripts only have access to the workbook, not to the machine hosting it, which matters when IT reviews what is allowed to run.

To use Office Scripts you need a commercial or education Microsoft 365 licence that includes the desktop apps (Business Standard, Business Premium, Apps for business and the Enterprise plans are on Microsoft’s list), OneDrive for Business, and Excel for Windows version 2210 or later, Excel for Mac or Excel for the web. Your admin can also switch the feature off for the organization, which is the usual reason the Automate tab is missing.

Here is what a small Office Script looks like. It stamps the report sheet with the date it was updated:

function main(workbook: ExcelScript.Workbook) {
  const sheet = workbook.getWorksheet("Report");
  const stamp = sheet.getRange("A1");
  stamp.setValue(`Updated ${new Date().toLocaleDateString()}`);
  stamp.getFormat().getFont().setBold(true);
}

And a VBA macro that refreshes every query and saves the report sheet as a PDF next to the workbook:

Sub RefreshAndExportPdf()
    ThisWorkbook.RefreshAll
    ThisWorkbook.Worksheets("Report").ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=ThisWorkbook.Path & "\Report_" & Format(Date, "yyyy-mm") & ".pdf"
End Sub

For the macro to wait until the data is in, turn off Enable background refresh in each query’s properties. Otherwise RefreshAll starts the refresh and the PDF is created from the old numbers.

Is VBA dying?

No, but it’s under more pressure than it used to be, and three changes are worth knowing about.

Macros in files from the internet are blocked by default. Since 2022 (Current Channel version 2206), Excel for Windows blocks VBA in files that carry the Mark of the Web, such as email attachments and browser downloads, and shows a Security Risk banner with no Enable button. Users have to unblock the file in its Properties, or IT has to set up trusted locations, trusted sites or signed macros. Files opened from OneDrive or SharePoint with Open in Desktop App don’t get the mark. If you send macro workbooks to clients or colleagues by email, plan for this.

VBScript is being phased out of Windows. Microsoft’s timeline keeps it enabled by default until around 2026 or 2027, then disables it by default, and later removes it. This doesn’t remove VBA, but macros that call .vbs files or reference the VBScript regular expressions library will need attention. From Microsoft 365 version 2508, the RegExp classes are built into VBA, so updated Office versions keep regex support without the external library.

VBA doesn’t follow your workbook to the web. As more teams open files in the browser or in Teams, a macro button that does nothing there becomes a support question.

Microsoft has not announced an end date for VBA in desktop Excel, and its own documentation says VBA still covers more of Excel’s desktop features than Office Scripts do, and that Office Scripts don’t support events. For desktop work involving Outlook, the file system or other Office apps, VBA remains the practical choice. I’d just avoid using it for jobs Power Query does better, such as importing and cleaning data, because that code is the hardest to maintain.

VBA vs Python, and where Copilot fits

Python in Excel is often pitched as the VBA replacement, but the two barely overlap. VBA automates actions; Python in Excel analyses data. It’s available to Microsoft 365 Business and Enterprise users on Windows (Current Channel) at standard compute speed, with a paid add-on for premium compute and more calculation modes. Device-based licences don’t support it. If you want a regression, a forecast or a cleaner chart inside the workbook, it’s a good tool. For refreshing a report and emailing a PDF, it isn’t.

Copilot and other AI assistants can draft formulas and VBA or Office Scripts code. That saves time on a first draft, and I’d check the output as carefully as code from a new colleague. One concrete change: the COPILOT worksheet function, offered as a preview, stopped working on September 14, 2026. Cells that used it keep their cached results but return #NAME? when they recalculate. If any of your reports used it, now is the time to replace it.

The hybrid setup I use for most reports

For a typical monthly report at an SME, this setup covers most needs:

  1. Power Query imports the exports or the folder of files, cleans them and runs checks (row counts, control totals, unmatched codes).
  2. The data lands in Excel tables or the Data Model, and PivotTables or formulas build the report.
  3. If the report has to go out as a PDF or email, a short VBA macro handles that last step, so the macro stays small and easy to read.
  4. If the team works in the browser or needs it to run on a schedule, an Office Script with a Power Automate flow takes the place of the macro. Check first that your data sources can refresh in Excel for the web, because not all of them can.

The point of this split is that each piece can be fixed by someone who understands just that piece. I described the macro side in more detail in how to automate monthly reports with Excel macros.

Frequently asked questions

Is Power Query better than VBA?

For importing, combining and cleaning data, yes. It’s faster to build, easier for someone else to follow and doesn’t trigger macro security warnings. VBA is better for actions Power Query can’t do, such as creating PDFs, sending emails or responding to button clicks.

Can Office Scripts replace VBA?

Partly. Office Scripts cover most workbook tasks and work in the browser and with Power Automate. They can’t respond to Excel events and they can’t work with your computer’s files or with Outlook directly, and they require a business or education Microsoft 365 licence.

Will VBA stop working?

Microsoft has not announced an end date for VBA in desktop Excel. The VBScript deprecation affects macros that call .vbs files or use the VBScript regex library, and macros in files downloaded from the internet are blocked by default until the file is unblocked or trusted.

Do I need to know how to code to automate Excel reports?

Not for Power Query, which is driven through the ribbon and records your steps. The Action Recorder for Office Scripts and the macro recorder for VBA also produce code for you, though recorded code usually needs some cleanup before it’s reliable.

Should I learn Python or VBA for Excel?

If your goal is automating report production in Excel, learn Power Query first, then a little VBA. Python is worth learning for analysis work, inside or outside Excel.

Not sure which fits your report?

Every team’s reports, licences and IT rules are different, and the right mix depends on all three. If you’d like a second opinion, book a free 20-minute call. Tell me how the report is built today and I’ll tell you which approach I’d use, and what it would take.