Why does your macro-enabled workbook stop working when uploaded to SharePoint? Why does it run fine in Excel Desktop but freeze on the browser version? Why does it work for your IT admin but not for your sales team?
Quick Answer
No — Excel macros (VBA) do not run in SharePoint Online’s web interface or Excel for the web. But yes — they can run if users open the file in Excel Desktop while connected to SharePoint (via OneDrive sync or direct link), and if macro settings are correctly configured on their local machine. The catch? It’s entirely client-side — SharePoint itself never executes VBA.
All the Methods
| Method | Steps | Best For | Limitations |
|---|---|---|---|
| Open in Excel Desktop via SharePoint link | Click "Open in Desktop App" from SharePoint ribbon → Enable content when prompted → Run macros manually or via shortcut | Teams using Windows + Excel 2016+, with Group Policy allowing trusted locations | Fails if user has Excel Online set as default app; requires macro security level at Medium or lower |
| Sync folder with OneDrive + local Excel | Sync SharePoint document library to local OneDrive folder → Open C:\Users\Alex\OneDrive - Acme Corp\Reports\Q3-Sales.xlsm in Excel Desktop |
Power users who need reliable macro execution & offline access | Macros won’t trigger automatically on file open unless trusted location is added; sync delays may cause version conflicts |
| Excel Add-ins (Office JS) | Deploy custom Office JS add-in via App Catalog → Load in Excel Online → Use Excel.run() API instead of VBA |
Cross-platform compatibility (Mac, Web, iPad); no VBA dependency | Cannot interact with Windows APIs, File System, or legacy COM objects; steep learning curve for VBA devs |
| Power Automate + Excel Online | Trigger flow on file update → Use "Get rows" action → Apply logic → Write back to range (e.g., B2:C10) | Simple data refreshes, notifications, or conditional formatting triggers | No cell-level automation (e.g., OnChange events); max 5,000 rows per action; can’t mimic UserForm or MsgBox |
| Macro-free fallback (Dynamic Arrays + LET) | Replace Sub RefreshReport() with formula-driven logic in E1: =LET(data,A2:C12, total,SUM(INDEX(data,,3)), HSTACK("Total:",total)) |
Lightweight reporting where macro logic is simple math or lookup | No loops, no file I/O, no event handling — only static or triggered recalculation |
Method 1 Deep Dive
Let’s say Sarah Chen uploads Inventory-Tracker.xlsm to her team’s SharePoint site (https://acmecorp.sharepoint.com/sites/ops/Shared%20Documents/Inventory-Tracker.xlsm). She expects her UpdateStockLevels() macro — which pulls from a local CSV and writes to Sheet1!A2:D50 — to run on demand. It doesn’t.
The fix starts with behavior: Right-click the file in SharePoint → "Open in Desktop App". Excel launches locally. If you see "Security Warning: Macros have been disabled" at the top, click "Enable Content". Now press Alt+F8. Your macro appears — and runs.
But here’s what most miss: SharePoint doesn’t control that prompt. It’s Excel Desktop checking its own Trusted Locations list. If the synced folder isn’t added as a Trusted Location (File → Options → Trust Center → Trusted Locations → Add new), macros stay blocked — even after clicking "Enable Content" once. Add C:\Users\Sarah\OneDrive - Acme Corp\Sites\Ops\Shared Documents as a trusted path. Then restart Excel.
Sample data in Sheet1 before macro:
| Item ID | Product | On Hand | Last Updated |
|---|---|---|---|
| ITM-8821 | Wireless Headset Pro | 142 | 2024-03-15 |
| ITM-9107 | USB-C Dock Station | 89 | 2024-03-12 |
| ITM-4430 | Mechanical Keyboard RGB | 203 | 2024-03-10 |
| ITM-2219 | Ergo Mouse Wireless | 57 | 2024-03-08 |
After running UpdateStockLevels(), cells A2:D50 refresh — including timestamps in column D. The beauty of this approach is zero code changes. Just policy + awareness.
Method 2 Deep Dive
Now imagine you’re supporting 200+ field reps — all on MacBooks or Chromebooks. They’ll never get Excel Desktop. So you pivot to Office JavaScript APIs.
Create an add-in manifest pointing to a hosted HTML page. In functions.js, write:
Excel.run(async (context) => {
const sheet = context.workbook.worksheets.getItem("Dashboard");
const range = sheet.getRange("B2:C10");
range.load("values");
await context.sync();
// Simulate logic: if Sales > $50k, flag green
const data = range.values;
for (let i = 0; i < data.length; i++) {
if (data[i][1] && data[i][1] > 50000) {
sheet.getRange(`C${i+2}`).format.fill.color = "#d1e7dd";
}
}
});
Deploy to SharePoint’s App Catalog. Users install it via "Insert → My Add-ins". It runs inside Excel Online — no desktop required. What makes this elegant is that it works identically on iPad, Edge, Safari, and even Excel for Android.
But here’s the counterintuitive tip: You can call VBA from Office JS — not directly, but via a hybrid trick. Embed a hidden button in your add-in UI that triggers document.location = "ms-excel:ofe|u|https://acmecorp.sharepoint.com/sites/ops/Shared%20Documents/Report.xlsm". That forces Excel Desktop open with the file — then your VBA runs. Not pure, but effective for edge cases.
Cheat Sheet
| Action | Shortcut / Command | Where It Works |
|---|---|---|
| Open macro file in Excel Desktop from SharePoint | Right-click file → "Open in Desktop App" | Windows + Excel 2016+, Edge/Chrome |
| Enable macros on first launch | Alt+F8 → then click "Enable Content" banner | Only in Excel Desktop, not Online |
| Add synced folder as Trusted Location | File → Options → Trust Center → Trusted Locations → Add | Prevents repeated prompts per file |
| Run macro via keyboard | Alt+F8 → select macro → Enter | Works only after enabling content |
| Test if macros are blocked | Check Developer tab → "Macros" button is grayed out? | Indicates security level too high or untrusted location |
| Verify macro storage | File → Info → "Related Documents" → "View Properties" → check "File Type" says .xlsm | .xlsx files silently strip VBA on upload |
