VBA vs Google Apps Script

The decision in one line

If the automation must run on a schedule without anyone present, use Apps Script. If it must manipulate Excel itself — formatting, printing, generating workbooks, driving dialogs — use VBA. Almost everything else follows from where your data already lives.

Side by side

Where VBA is still the right answer

Anything that touches the Excel object model

Applying conditional formats in a loop, resizing and grouping, setting print areas, iterating shapes on a chart sheet. Apps Script has no equivalent surface because Google Sheets does not expose one.

Generating one file per row

Producing 400 individually formatted workbooks or PDFs from a source table is a classic VBA job and has no execution-time ceiling. The same job in Apps Script requires batching around the six-minute limit.

When the data cannot leave the building

Regulated environments where nothing may reach a cloud service. VBA runs entirely on the machine.

The cost of choosing VBA

The .xlsm file itself. Many organisations block macro-enabled attachments at the mail gateway and disable macros by policy, so distribution becomes a signing and trust-centre conversation. Factor that in before choosing it for something that has to reach twenty people.

Where Apps Script wins outright

Scheduled work

This is the decisive one. A trigger that runs at 6am every weekday, pulls yesterday's numbers, refreshes a summary and emails it, keeps working when the person who built it is on holiday. There is no VBA equivalent that does not involve a machine left switched on.

function dailyRefresh() {
  const sheet = SpreadsheetApp.openById(SHEET_ID).getSheetByName('Raw');
  const rows = fetchFromApi_();            // UrlFetchApp, with retry
  sheet.getRange(2, 1, rows.length, rows[0].length).setValues(rows);
  MailApp.sendEmail(RECIPIENTS, 'Daily numbers', buildSummary_());
}

Talking to other systems

UrlFetchApp makes any REST API a few lines of work, and OAuth to other Google services is handled for you. In VBA the same call is a WinHTTP object and manual JSON parsing.

Multi-user files

If several people are in the sheet while the script runs, Apps Script has LockService to serialise writes. VBA has no concept of this because the scenario does not arise.

The mistakes we are most often called in to fix

• VBA used to fetch and reshape data. Power Query does this better, keeps the file as a plain .xlsx, and is inspectable by someone who is not a developer.
• Apps Script written as if it had no quotas. A loop calling getRange().getValue() per row will hit the six-minute limit on a few thousand rows. Read once into an array, process in memory, write once.
• No failure path. Both platforms will happily write wrong numbers forever. Validate the shape of the input and fail loudly.
• Automating the wrong layer. If the underlying process is broken, automating it produces broken output faster.