It's 4:47 PM on Friday. Your manager just asked for a consolidated report by 5. You have 12 spreadsheets open—six shared via SharePoint, three in OneDrive, two emailed as attachments—and you need to apply the same formatting, insert a timestamp in cell A1, and auto-calculate YTD variance across all sheets. You double-click the Macros button in Excel Online… and it’s grayed out.
Quick Answer
No—macros written in VBA do not run in Excel Online. The platform lacks the VBA engine entirely. But yes—you can achieve macro-like automation using Office Scripts (TypeScript-based), Power Automate flows triggered from Excel Online, or hybrid workflows that offload VBA execution to desktop Excel while keeping data in the cloud.
All the Methods
| Method | Steps | Best For | Limitations |
|---|---|---|---|
| Office Scripts | Record or write TypeScript in Excel Online → Save to workbook → Run with one click or schedule via Power Automate | Formatting, data cleanup, row insertion, conditional logic on tables | No file I/O, no external API calls, no user input prompts, no looping over closed workbooks |
| Power Automate + Excel Online connector | Create flow → Trigger on file update or button click → Use 'Get rows', 'Update row', 'Add row' actions → Chain with approval or email steps | Cross-workbook updates, notifications, approvals, scheduled refreshes | No cell-level formatting control; max 5,000 rows per action; requires Microsoft 365 E3/E5 or Business Premium |
| Desktop Excel + Cloud Sync | Save workbook to OneDrive/SharePoint → Open in desktop Excel → Run VBA macro → Changes sync automatically to cloud | Full VBA support: UserForms, MsgBox, FileSystemObject, Windows API calls | Requires desktop app installed; doesn’t work on iPad or Chromebook; won’t trigger if user only uses browser |
| Excel Add-ins (web-based) | Install add-in like 'Power Tools' or 'Slicers Pro' → Enable in Excel Online → Use ribbon commands | Reusable functions (e.g., remove duplicates, merge columns) without scripting | Limited customization; add-ins must be approved by tenant admin; no access to worksheet event handlers |
| Custom JavaScript API (advanced) | Build task pane add-in using Office.js → Deploy via AppSource or sideloading → Interact with Excel Online DOM | Enterprise teams building branded, scalable automation tools | Requires developer license, TypeScript knowledge, and IT admin deployment rights |
Method 1 Deep Dive
Let’s walk through an Office Script that formats a sales summary table — the kind you’d normally handle with a 12-line VBA sub. Open Excel Online, go to the Automate tab → New Script. Paste this:
function main(workbook: ExcelScript.Workbook) {
const sheet = workbook.getActiveWorksheet();
const range = sheet.getRange("A1:E10");
range.getFormat().autofitColumns();
sheet.getRange("A1:E1").getFormat().fill.setColor("#0f766e");
sheet.getRange("A1:E1").getFormat().font.setColor("white");
const table = sheet.tables.add("A1:E10", true);
table.getSort().apply([{key: 4, ascending: false}]); // Sort by column E (Revenue)
}
Click Run. Instantly, your table headers turn forest green, columns auto-fit, and rows sort descending by Revenue. The beauty of this approach is that it’s portable: save the script, then reuse it on any workbook—even ones shared with colleagues who’ve never touched code. Try it on this sample data:
| Rep | Region | Q1 Sales | Q2 Sales | Revenue |
|---|---|---|---|---|
| Sarah Chen | APAC | $24,800 | $31,200 | $56,000 |
| Diego Mora | EMEA | $18,500 | $29,700 | $48,200 |
| Jamal Wright | NA | $22,100 | $26,900 | $49,000 |
| Priya Kapoor | APAC | $15,300 | $33,400 | $48,700 |
| Marcus Bell | EMEA | $27,600 | $21,100 | $48,700 |
| Tasha Lee | NA | $19,900 | $28,300 | $48,200 |
Note the surprise: Office Scripts *can* sort tables—but only if the range is converted to a formal Excel Table first (sheet.tables.add()). That’s what most people miss. Without that line, table.getSort() throws an error. Also: Alt+A+T opens the Automate tab—no mouse needed.
Method 2 Deep Dive
Now let’s use Power Automate to solve the Friday 4:47 PM problem. You need to pull data from six separate files named Sales_Q1_2024.xlsx through Sales_Q6_2024.xlsx, all stored in a SharePoint folder called Regional Reports.
Create a new flow: Automated cloud flow → Trigger: When a file is created or modified in a folder (point to your SharePoint folder). Then add these actions:
- Get rows (Excel Online connector) → Select file, worksheet name, and range (e.g.,
A2:E100) - Apply to each → Select the output of ‘Get rows’
- Append to array variable → Store each row as object with added property
"Quarter": "Q1" - Create table (in target workbook) → Write consolidated array to
Consolidated!A1
Set it to run when any file changes. Now every time someone uploads Sales_Q4_2024.xlsx, the flow runs automatically. What makes this elegant is that it works even if the source files contain formulas, merged cells, or custom number formats—the flow reads values only, so no VBA runtime errors. And yes—it handles dates like 2024-03-15 and currency like $45,200 natively.
Test it with real data: In Sales_Q3_2024.xlsx, rows A2:E7 contain:
| Product | Country | Units | Price | Total |
|---|---|---|---|---|
| CloudSync Pro | Germany | 142 | $299 | $42,458 |
| CloudSync Pro | Japan | 89 | $325 | $28,925 |
| DataVault Lite | Canada | 203 | $149 | $30,247 |
| DataVault Lite | Australia | 167 | $159 | $26,553 |
Your flow will append all four rows to the master sheet with a new column Quarter = "Q3". No desktop required. No macro security warnings.
Cheat Sheet
| Task | How to Do It | Shortcut / Tip |
|---|---|---|
| Open Automate tab | Click Automate → New Script or My Scripts | Alt+A+T |
| Run existing script | Go to Automate → My Scripts → Click script name → Run | Scripts save per workbook—not globally |
| Trigger Power Automate from Excel | Insert → Action → Button → Link to flow URL | Use ‘Button’ control — works in Excel Online |
| Force VBA sync from desktop | Open cloud file in desktop Excel → Run macro → Save → Changes appear online within 10 sec | Works even if file was last opened in browser |
| Check script compatibility | Avoid Workbook.SaveAs, MsgBox, Application.Wait |
Use console.log() for debugging instead |
