Power Query vs VBA macros: when to switch
Comparison · By Tahir Nas · Examples run in our simulator · Updated
9 min read
Switch from VBA to Power Query when the macro's job is getting, cleaning and combining data: importing files, deleting blank rows, filling down, stacking sheets, looking up values. Keep VBA for what Power Query can't do, such as formatting sheets, driving other Office apps, reacting to events or writing data back. Many workbooks end up with both: Power Query does the data preparation and a short macro, if any, handles the rest.
What each tool is for
VBA (Visual Basic for Applications) is the programming language built into the desktop Office apps. A macro can do almost anything a user can do in Excel: open files, copy ranges, format cells, show dialogs, create charts, send data to Word or Outlook. That generality is its strength and its cost, because every step is code you write and maintain.
Power Query is a data preparation engine. Microsoft describes it as a tool for getting data from sources and applying transformations, recorded as a repeatable query you can refresh (What is Power Query?, Microsoft Learn). It does one family of jobs (get, reshape, combine, load) and does them without macros.
So the question isn't which tool is better. It's which parts of your macro are really data preparation.
Jobs that move cleanly to Power Query
Many data macros follow the same pattern: open some files or sheets, copy the data into one place, delete the junk rows, fix a few columns, and paste the result somewhere a pivot table can read it. Every one of those steps has a direct Power Query equivalent. The post on replacing a monthly copy-paste process rebuilds that routine step by step.
| Typical macro task | Power Query equivalent |
|---|---|
| Loop through files in a folder and copy each one in | Folder connector and Combine Files |
| Copy each month's sheet under the last | Append queries (Table.Combine) |
| Delete rows where every cell is blank | Filter rows (Table.SelectRows) |
| Fill empty cells with the value above | Fill Down (Table.FillDown) |
| VLOOKUP a value from another sheet | Merge queries (Table.NestedJoin) |
| Split a column on a delimiter | Split Column |
| Turn month columns into rows | Unpivot Other Columns |
Here is the core of a typical "stack the monthly sheets and tidy them" macro, rebuilt in M. Two monthly tables arrive with a blank row and with the region written only once per block, as printed reports often are:
let
Jan = #table({"Region", "Product", "Units"}, {{"North", "Kite", 12}, {null, "Reel", 4}, {null, null, null}, {"South", "Kite", 9}}),
Feb = #table({"Region", "Product", "Units"}, {{"North", "Kite", 15}, {null, "Line", 7}}),
Tagged = Table.Combine({
Table.AddColumn(Jan, "Month", each "2026-01", type text),
Table.AddColumn(Feb, "Month", each "2026-02", type text)
}),
NoBlankRows = Table.SelectRows(Tagged, each not List.IsEmpty(List.RemoveNulls({[Region], [Product], [Units]}))),
Filled = Table.FillDown(NoBlankRows, {"Region"})
in
FilledResult
| Region | Product | Units | Month |
|---|---|---|---|
| North | Kite | 12 | 2026-01 |
| North | Reel | 4 | 2026-01 |
| South | Kite | 9 | 2026-01 |
| North | Kite | 15 | 2026-02 |
| North | Line | 7 | 2026-02 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
The result is five clean rows, each with its region filled in and a Month column showing where it came from. The blank row is gone, and the Reel and Line rows picked up "North" from the row above. In a real workbook the two tables would come from sheets or files rather than #table, but the steps after the source are the same. One caution: fill down runs across the combined table, so if a month's first row had a blank Region it would inherit the previous month's last region. If that can happen in your data, fill down each table before combining them.
Three things are better about this version than the macro it replaces:
- It reruns safely. Refresh replaces the output rather than pasting below the last run, so there is no risk of doubling the data by clicking twice.
- It's readable without code. Each step appears by name in Applied Steps, and a colleague can click through them and see the table at each point.
- The file stays an .xlsx. No macro-enabled workbook and no macro security prompt.
The lessons on appending many tables and tagging the source and filling down report layouts cover the two main steps in more depth, and combining files from a folder replaces the classic "loop through every file" macro.
Jobs where VBA is still the right tool
Power Query transforms data and loads the result; it doesn't control Excel itself. Keep VBA, or look at another tool, when the job is:
- Formatting and layout: colouring cells, setting print areas, building a formatted report sheet.
- User interaction: forms, input boxes, buttons that run different logic.
- Events: running something when a cell changes or a workbook opens.
- Other applications: creating Word documents, sending Outlook emails, saving PDFs.
- Writing back: updating a database or another workbook from Excel. Power Query reads sources; it doesn't change them.
Microsoft's comparison of VBA and Office Scripts also notes that VBA macros run in Excel for Windows and Excel for Mac but not in Excel on the web, and that VBA has no Power Automate connector (Differences between Office Scripts and VBA macros, Microsoft Learn). If you need automation that runs in the browser or on a schedule without anyone opening Excel, Office Scripts with Power Automate is the route to look at, which the post on Power Query vs Power Automate compares.
Security: a practical reason to switch
Macros are a known route for malware, and Microsoft changed Office's defaults because of it. Microsoft's documentation says Office now blocks VBA macros in files from the internet, such as email attachments, by default, and shows a security risk banner instead of an "Enable content" button (Macros from the internet are blocked by default in Office, Microsoft Learn). The same page says the change applies to Office on Windows and doesn't affect Office on a Mac or on the web.
For a report you email to colleagues, that matters. A macro-enabled workbook sent as an attachment may arrive with its macros blocked until the recipient unblocks the file or IT trusts its location. A workbook whose data preparation is in Power Query has no macros to block. It isn't free of prompts, though: Excel can still ask the recipient to enable external data connections before a query refreshes, and the recipient needs access to the query's sources, such as the folder path or the database credentials. The post on refreshing Power Query automatically in Excel covers that data-connection warning.
How to migrate a macro
Don't rewrite a large macro in one go. Treat it as a list of jobs and move them one at a time.
1. List what the macro does
Read the code, or step through it, and write each job as one line: "opens every CSV in the Input folder", "deletes rows where column A is blank", "looks up the manager from the Staff sheet", "formats the header row bold". Mark each one as data preparation or something else.
2. Rebuild the data preparation as a query
Build the query in the Power Query editor, one job per step, and give each step a name that says what it does. Compare the query's output with the macro's output on the same input, row count first, then a few totals.
3. Add the checks the macro had (or should have had)
Macros often contain defensive code: "if the Price column is missing, show a message and stop". Keep that behaviour. A query can stop with a clear error when the input changes shape:
let
Source = #table({"Product", "Units"}, {{"Kite", 12}, {"Reel", 4}}),
Required = {"Product", "Units", "Price"},
Missing = List.Difference(Required, Table.ColumnNames(Source)),
Checked = if List.IsEmpty(Missing) then Source else error Error.Record("Missing columns", Text.Combine(Missing, ", "), null)
in
CheckedResult: an error
Missing columns: Price
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Because the Price column is missing, the query stops with the error "Missing columns: Price" instead of loading a partial table. The lesson on validating data and failing loudly shows other checks worth adding.
4. Keep a small macro only if you need one
If something still needs VBA (formatting the output, saving a PDF), keep a short macro for that part only. If the macro also triggers the refresh, read Microsoft's note on Workbook.RefreshAll: when a Power Query query that loads to a worksheet table fails during refresh, RefreshAll doesn't raise a run-time error, other connections still refresh and the table keeps its previous data (Workbook.RefreshAll method, Microsoft Learn). The same page recommends refreshing each table's query with QueryTable.Refresh and BackgroundQuery set to False if you need to catch those errors:
Sub RefreshSalesTable()
' Refresh one Power Query table so a failure raises an error you can handle
ThisWorkbook.Worksheets("Report").ListObjects("Sales").QueryTable.Refresh BackgroundQuery:=False
End SubThat VBA snippet follows the documented pattern; it isn't M, so it wasn't run in this site's simulator.
A quick decision guide
| If the macro mainly… | Then |
|---|---|
| Imports, cleans and combines data | Move it to Power Query |
| Formats a report sheet | Keep VBA for that part |
| Needs to run in Excel on the web or on a schedule | Look at Office Scripts and Power Automate |
| Writes data back to a database or file | Keep VBA, or use a tool designed for writing |
| Does both data work and formatting | Split it: Power Query for data, a short macro for the rest |
If you're moving macro jobs across and want to practise the Power Query side, the Power Query for beginners guide covers the editor, and Power Query Exercises has browser-based tasks that mirror common macro jobs, such as turning a report with subtotals into a clean table and combining every monthly CSV in a folder.
Frequently asked questions
Is Power Query faster than VBA?
It depends on the job and on how the macro is written. A macro that loops cell by cell and updates the sheet as it goes can be slow, while one that reads ranges into arrays can be quick. Compare both on your own data before deciding on speed alone; maintainability is usually the stronger reason to switch.
Can Power Query run VBA, or VBA run Power Query?
Power Query can't run VBA. VBA can refresh Power Query queries, through RefreshAll or by refreshing a specific table's query, and it can read the results from the sheet once the refresh finishes.
Does Power Query work on a Mac?
Microsoft's product table lists Power Query Desktop for Excel for Mac as well as Excel for Windows (What is Power Query?, Microsoft Learn). Available connectors can differ between hosts, so check the connector you need before moving a Mac workflow across. The post on Power Query in Excel for Mac goes into the details.
Sources
- What is Power Query?, Microsoft Learn (learn.microsoft.com)
- Differences between Office Scripts and VBA macros, Microsoft Learn (learn.microsoft.com)
- Macros from the internet are blocked by default in Office, Microsoft Learn (learn.microsoft.com)
- Workbook.RefreshAll method, Microsoft Learn (learn.microsoft.com)
About the author
Tahir Nas, software engineer, writes and reviews the lessons, guides and exercises on Power Query Exercises. Tahir has 8 years of experience in tech, working across system administration, cloud architecture, QA, scripting and automation, and software engineering in many languages and mainstream databases. GitHub profile. Every M example shown with a result was run in the site's simulator when the page was built, and the output is that run's result; examples marked for Excel and Power BI only are shown without output. How content is tested.
Keep reading
- Power Query vs Power Automate: which tool for which jobPower Query shapes data; Power Automate runs workflows. Which jobs belong to each, where they overlap, and how to combine them without duplicating logic.
- How to refresh Power Query automatically in ExcelRefresh Power Query when an Excel workbook opens, every few minutes or on a timer with VBA, and make the queries safe to refresh when nobody is watching.
- Power Query vs Python pandas for data cleaningPower Query or pandas for cleaning data? The same tasks side by side (unpivot, merge, locale parsing), where each tool fits, and how they work together.
Practise Power Query in the browser with graded exercises checked against fresh data.
Power Query Exercises is an independent educational platform and is not affiliated with, endorsed by, sponsored by, or approved by Microsoft Corporation. All third-party trademarks belong to their respective owners.