Power QueryExercises

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 taskPower Query equivalent
Loop through files in a folder and copy each one inFolder connector and Combine Files
Copy each month's sheet under the lastAppend queries (Table.Combine)
Delete rows where every cell is blankFilter rows (Table.SelectRows)
Fill empty cells with the value aboveFill Down (Table.FillDown)
VLOOKUP a value from another sheetMerge queries (Table.NestedJoin)
Split a column on a delimiterSplit Column
Turn month columns into rowsUnpivot 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
    Filled

Result

RegionProductUnitsMonth
NorthKite122026-01
NorthReel42026-01
SouthKite92026-01
NorthKite152026-02
NorthLine72026-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:

  1. 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.
  2. 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.
  3. 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
    Checked

Result: 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 Sub

That 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 dataMove it to Power Query
Formats a report sheetKeep VBA for that part
Needs to run in Excel on the web or on a scheduleLook at Office Scripts and Power Automate
Writes data back to a database or fileKeep VBA, or use a tool designed for writing
Does both data work and formattingSplit 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

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

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.