Ravindra BagaleCourses & study guides मराठी Track your progress

12. Macros and VBA

12.21 VBA vs Office Scripts: Which One When?

By Last updated:

Example first. Amir has a VBA macro that tags late orders on his laptop. His company is moving the team to Excel on the web and Teams. Should he rewrite everything as Office Scripts? Not always. VBA is still the right tool for some jobs, and Office Scripts for others. This lesson puts Microsoft's comparison into one table and gives a simple rule.

VBA vs Office Scripts — in 30 seconds

What: VBA macros live inside a desktop workbook (.xlsm) and can control the whole computer. Office Scripts live in your OneDrive, are written in TypeScript, can only touch the workbook, and run on the web and from Power Automate.

Why: Choosing the right tool avoids rewriting work twice: desktop-only, file and Outlook automation stays in VBA; shared, cloud and scheduled automation fits Office Scripts.

Tiny example: The same "find late orders" job is a For loop in VBA (the TagLateOrders macro in this chapter's answer key) and a for loop in TypeScript (lesson 12.19). Only the Office Script can run from a Power Automate flow.

First look at the data (before → after):

VBA vs Office Scripts: before and after tables, fictional FreshGo Maharashtra data

Expected result — Office Script

Point Answer
Language TypeScript
Runs in Excel on the web Yes
Power Automate Yes (Run script)

About this data: Teaching sample for a fictional shop “Summit Schools”. Figures and names (e.g. Aarti Kulkarni) are made up for this lesson on vba vs office scripts which one when.

Before table and formula as text

Before — VBA macro

Point Answer
Language VBA
Runs in Excel on the web No
Power Automate No connector

About this data: Demo table only — not real customer or company data. Built to practise vba vs office scripts which one when.

Formula / click path

VBA:            For r = 2 To lastRow … If ws.Cells(r, "H").Value > 15 Then …
Office Scripts: for (let i = 0; i < values.length; i++) { if (mins > 15) { … } }

Do it step by step:

  1. List the macros you use every week and write next to each one where it must run: desktop only, or also the web.
  2. Mark the macros that open other files, use Outlook, or react to events such as opening the workbook or changing a cell.
  3. Keep those in VBA: Office Scripts cannot reach other files or apps, and they have no workbook events.
  4. Mark the jobs that colleagues run in the browser, or that should run on a timetable.
  5. Rebuild those as Office Scripts: record them first (lesson 12.18), then tidy the code (lesson 12.19).
  6. Test each script on a copy of the workbook before you switch off the old macro.

Microsoft's comparison, in one table

Point VBA macros Office Scripts
Excel on the web No Yes
Excel for Windows Yes Yes
Excel for Mac Yes Yes
Excel for iPhone / iPad No No
Power Automate No connector Yes (Run script)
Language Visual Basic for Applications TypeScript
What it can reach The whole desktop: other files, folders, Outlook, Word Only the workbook it runs on
Workbook events (on open, on change) Yes No
Where it is stored Inside the .xlsm workbook In your OneDrive, shared separately
Licence No special licence Work or school Microsoft 365 licence
Security model Macro security settings, Trust Center (lesson 12.1) Admin controls in Microsoft 365; scripts cannot touch your computer

About this data: Made-up rows so you can copy the pattern for vba vs office scripts which one when. Currencies may use ₹, £, € or $.

The same job in both languages (VBA from the TagLateOrders answer key earlier in this chapter)

Step VBA (TagLateOrders) Office Scripts (lesson 12.19)
Start Sub TagLateOrders() function main(workbook: ExcelScript.Workbook)
Find the data ws.Cells(ws.Rows.Count, "A").End(xlUp).Row for the last row workbook.getTable("tblOrders")
Loop For r = 2 To lastRow for (let i = 0; i < values.length; i++)
Test If ws.Cells(r, "H").Value > 15 Then if (mins > 15)
Mark the row ws.Cells(r, "J").Value = "Late" getFill().setColor("#FFE699") (colour instead of text)
Message MsgBox console.log (or return a value to Power Automate)

About this data: Fictional worksheet used in class. Safe to edit; nothing here is live production data.

A simple rule

  1. Must it run in the browser, in Teams, or on a schedule with nobody at the desk? Choose Office Scripts.
  2. Must it open other files, send Outlook mail from your PC, or react when a cell changes? Choose VBA.
  3. Both? Keep the VBA for the desktop part, and use Power Automate for the email or schedule part with an Office Script for the Excel part.
  4. No work or school licence? Office Scripts are not available (except an Insider preview for Personal and Family plans), so VBA it is.

Microsoft sources: Differences between Office Scripts and VBA macros; Platform limits, requirements and error messages for Office Scripts.

Practice task (try it before you open the answer):

  1. Sort these jobs into VBA or Office Scripts: (a) every night, count late orders in a shared OneDrive workbook; (b) when a cell in column H changes, colour the row; (c) combine 30 workbooks from a folder on your laptop; (d) a button colleagues press in Excel on the web.
  2. Explain your answer for (b) in one sentence.
Answer
  1. (a) Office Scripts with a scheduled Power Automate flow. (b) VBA, because it needs a worksheet change event. (c) VBA (or Power Query, chapter 15), because it opens files on your computer. (d) Office Scripts, because VBA does not run in Excel on the web.
  2. Office Scripts have no workbook events, so "when a cell changes" needs a VBA Worksheet_Change macro (or conditional formatting, which needs no code at all).

Ravindra Bagale's Tip

Before automating anything, ask whether a formula, conditional formatting or Power Query can do it. The best macro is often the one you did not need to write.

Worked example — vba vs office scripts which one when

Why

Sana Sheikh in Pune and James O'Neill abroad both need a clear, repeatable way to use vba vs office scripts which one when on a QuickCart Delivery sheet without breaking totals.

What

Vba Vs Office Scripts Which One When is the Excel idea this page teaches. We will walk one tiny example end to end.

How

  1. Open a blank sheet. In A1 type a short label for vba vs office scripts which one when.
  2. Enter a small made-up value for Sana Sheikh using € where money appears.
  3. Add a second value for James O'Neill using $ so you see mixed currencies stay numeric.
  4. Apply the method from this lesson step by step (menu or formula as shown above).
  5. Check the result still works inside SUM or a simple check cell.

Formula / action

Follow the formula or ribbon steps shown in this lesson (keep values numeric).
Result: The demo cell shows the expected result for QuickCart Delivery's sample row.
Meaning: Once vba vs office scripts which one when is correct on one row, copy the same pattern down the column.

Practice tasks (10+)

Try these on a blank sheet. Open a hint only if you are stuck. No quiz — just practice.

1. Rebuild the smallest example of vba vs office scripts which one when on a blank sheet for BrightLedger Finance.

Hint: Use only the steps on this page. Keep numbers numeric.

2. Change one input for Neha Shah and confirm the result updates.

Hint: If the result is stuck, check absolute vs relative references.

3. Repeat the same steps with a £ or € value for Emma Clarke.

Hint: Formatting must not break SUM.

4. Write the result meaning in one plain sentence next to the cell.

Hint: Imagine explaining it to a new joiners.

5. Add a second row and fill the method down.

Hint: Double-click the fill handle if the pattern is consistent.

6. Break the example on purpose, then fix it using this lesson.

Hint: Common break: text instead of a number.

7. Apply vba vs office scripts which one when to a 5-row made-up BrightLedger Finance table.

Hint: Keep column headers clear.

8. Check your answer with a simple total or COUNT.

Hint: A wrong total usually means a format or key mismatch.

9. Swap ₹ and $ on one column and re-check totals.

Hint: Stored values stay the same; only the display changes.

10. List two mistakes beginners make with vba vs office scripts which one when.

Hint: Look back at the tip box on this page.

11. Teach the steps out loud in under one minute.

Hint: Why → What → How.

12. Save the sheet as BrightLedger_practice.xlsx.

Hint: Use a clear file name for class.

Loading practice tasks...

  1. Last used row (Easy): Which row number is the last filled row? The headings are in row 1.
  2. Late orders (Easy): How many rows have Status "Late"?
  3. Total amount (Easy): Add up the Amount column.
  4. Worst delay (Easy): What is the largest number in the Days late column?
  5. Late orders in Pune (Medium): How many orders are Late and in City "Pune"?
  6. Customer for an order (Medium): Find the Customer for Order ID "DL-704".
  7. Money stuck in late orders (Medium): Add up the Amount column for rows where Status is "Late".
  8. Big orders (Medium): How many rows have an Amount of 2000 or more?
  9. Message text (Medium): Build the text "N late orders", where N is the count of Late rows.
  10. Average delay of late orders (Hard): What is the average Days late for rows with Status "Late"? Round to 1 decimal.
  11. Tag for the first row (Hard): For the first row, return "Chase" if Days late is 3 or more, else "OK".
  12. Warning or all clear (Hard): Return "Warning" if any order is 3 or more days late, else "All clear".

See all practice tasks in the Task Hub →

Quick revision cards

Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.

Revision card 1: What is Vba Vs Office Scripts Which One When?
Card 1: What is Vba Vs Office Scripts Which One When? · Download PNG
Revision card 2: Tiny example — Vba Vs Office Scripts Which One When
Card 2: Tiny example — Vba Vs Office Scripts Which One When · Download PNG
Revision card 3: Checklist — Vba Vs Office Scripts Which One When
Card 3: Checklist — Vba Vs Office Scripts Which One When · Download PNG