Every office has the same chore: copy values between sheets, format the monthly report, split one workbook into twenty, save a PDF, send the mail. A macro would solve it, but nobody can write one. An AI assistant closes that gap: you describe the task, it writes the macro, explains it and repairs it when it fails. A chore of 30-60 minutes becomes a one-click run, built in an afternoon.
Problem & value
- Today: repetitive Office work is done by hand or depends on one colleague's old VBA file nobody dares to touch. Recorded macros break when the layout changes; IT has no capacity for small requests.
- AI value: the assistant writes code from a description, explains foreign macros, adds error handling and translates between dialects (VBA to Office Scripts, Excel to Apps Script).
- Benefit: no reliable sourced figure exists for macro generation, so measure it: minutes saved per run times runs per month, minus build time.
Variants
- Excel VBA: the classic; desktop Excel on Windows, limited on Mac, not in Excel for the web.
- Office Scripts (TypeScript): run in Excel on the web, triggerable from Power Automate, shareable with everyone who opens the workbook [1].
- Google Apps Script: JavaScript attached to Sheets, Docs, Gmail or Drive, with time-based triggers [2].
- Word / Outlook / PowerPoint VBA: batch formatting, mails from templates, slide exports.
- Private use: household budgets, club member lists.
How it works
task description + file structure (anonymised sample)
-> AI assistant generates macro + explanation
-> paste into editor (VBA / Office Scripts / Apps Script)
-> test on a COPY -> error message back to AI -> fix
-> human review of what the macro touches (files, mails, deletions)
-> run via button, shortcut or Power Automate / Apps Script trigger
- Key component: a code-capable LLM. It needs the structure (headers, formats, rules), not your data. Describe the layout, do not upload the table.
- Human-in-the-loop: test on a copy; a second person reviews macros that delete, overwrite or send.
- Microsoft documents that scheduling inside Office Scripts is currently disabled, so scheduled runs go through Power Automate [1]. Copilot features differ by licence, platform and language; verify in your tenant.
Data preparation
Which data: hardly any business data: a precise task description, the file structure with 3-5 anonymised rows, and the desired result as example.
Where it lives: Excel/Word files on SharePoint, OneDrive, Nextcloud or file shares, plus the know-how of the people doing the task.
Typical gaps: merged cells, several tables per sheet, mixed date formats, undocumented exceptions, macros hidden in personal workbooks.
Preparation steps (a few hours): 1. Write the task as numbered manual steps including exceptions (1 hour). 2. Make the sheet machine-friendly: one header row, named tables, consistent formats (PREP-011, AUTO-006). 3. Create dummy sample data without names or IBANs (PREP-022). 4. Set up a shared library with an owner per macro; find existing shadow macros (PREP-003). 5. Define what may be pasted into which AI tool (PREP-024, GUIDE-007).
Effort share: low, about 10-20 percent, mostly tidying the sheet. See GUIDE-003.
Implementation paths
Which path for whom
- SMB - A: macro-writing assistants are mature, EU-hostable and priced per seat; the task is standard and prompts contain code, not data.
- Enterprise - B: governance matters: a central API assistant with logging, policy prompt, review step and script library beats uncontrolled personal accounts.
- Private - A: consumer assistants or AI in Sheets suffice; LM Studio or Ollama for sensitive files.
Path A - Buy (recommended for SMBs)
- In the suite: Microsoft 365 Copilot (Agent Mode in Excel, also with Claude models once the admin enables Anthropic as sub-processor [3]); Gemini in Google Workspace for Sheets and Apps Script.
- Chat assistants: ChatGPT, Claude, Gemini, Microsoft Copilot Chat, Le Chat (Mistral, EU provider). Use business plans with AVV and training opt-out, not consumer accounts.
- Effort: 1-4 hours per first macro; one afternoon of coaching for five people.
Path B - Build on cloud APIs
- Internal macro assistant with a system prompt (naming, error handling, no external calls) on Azure OpenAI (EU data zone), Mistral API (EU) or AWS Bedrock Frankfurt, delivered as Teams bot or n8n workflow.
- Add a versioned script library, automatic checks for forbidden calls (
Shell,URLDownloadToFile) and four-eyes release. - Effort: 3-10 days; token cost is small.
Path C - Self-host (open source)
- Ollama or LM Studio with a code model (e.g. Qwen2.5-Coder, Codestral) and Continue or Open WebUI.
- Hardware: HW1 for 7B models (simple macros); HW2 for 14-32B models that handle longer VBA. Setup half a day; quality below top cloud models.
- Choose C for sensitive structures where no cloud AI is allowed, or when the server also serves AUTO-018, AUTO-020 and CODE-001.
Path D - Custom training
Not worth it; prompts with your conventions and example macros are enough.
State of the art (2026)
- Current code models write correct VBA, Office Scripts and Apps Script for typical office tasks in one or two attempts; Excel Agent Mode can also edit workbooks directly instead of producing code.
- Microsoft reported 57.2 percent accuracy for Excel Agent Mode on SpreadsheetBench (912 tasks; vendor-reported, humans scored 71.3 percent per press) [5]: errors on complex tasks remain common.
- Failure modes: invented methods (esp. Office Scripts, Mac VBA), edge cases (empty rows, text in number columns), wrong regional settings (comma vs. dot), silent data loss through
Clear/Delete, unmaintained shadow IT. - Office blocks internet-sourced macro files by default; plan signing and trusted locations [4].
Shared resources & combinations
- Same capability (llm-code): AUTO-018, AUTO-019, AUTO-020, AUTO-021, CODE-001; one approved assistant and prompt library serve all; one HW2 host too.
- Combine with: AUTO-001 and AUTO-006 first, macros when a formula is not enough; AUTO-015 and AUTO-007 as run-time automation; CODE-007 and CODE-012 when the macro grows into an app or integration.
Effort, cost & KPIs
- A: 1-4 hours per macro; existing licences or a business AI seat. B: 3-10 days setup plus tokens. C: half a day to two days plus hardware.
- KPIs: chores automated, minutes saved per month, share of macros with owner and documentation, share reviewed before use, macro incidents.
People & change
Involve: department leads (chores), end users (exceptions), IT (macro policy, signing), information security (what a macro may do), DPO (what may enter prompts); management sets the AI policy and names a champion per department.
Works council: depends. The AI tool that logs prompts and activity is objectively suited to monitor behaviour under §87(1) no. 6 BetrVG (BAG: suitability suffices), and macros that record who processed what can be too. §90(1) no. 3 requires timely information on AI-supported work processes. Put a clause in the AI works agreement: no performance evaluation from prompt logs (GUIDE-004). Public bodies involve the Personalrat.
AI literacy: Since 27 Jul 2026, Art. 4 AI Act requires providers and deployers to take measures that support the development of staff AI literacy (no guaranteed level). A half-day workshop: precise prompts, testing on a copy, reading errors, what never enters a prompt (GUIDE-006).
Pilot and communication: frame it as getting rid of boring chores; pilot with 3-5 curious staff for four weeks, share macros in the library, let champions demo. Concerns: "will I be replaced" and "am I allowed to" - answer with clear rules and approved tools (GUIDE-008, GUIDE-009).
Humans in control: no unreviewed macro on production data.
Risks, regulation & compliance
- EU AI Act: minimal risk; Art. 4 applies. Macros supporting decisions about people (HR scoring) leave this scope; check Annex III (GUIDE-014).
- GDPR: prompts hold structure, not personal data. If real data is pasted: AVV (Art. 28), EU hosting; DPIA normally not needed, check if macros process employee data at scale (GUIDE-005).
- Security: AI code may call external servers, delete files or contain credentials; macros are a known malware vector. Sign macros, use trusted locations, never run code you do not understand (GUIDE-013).
- Bookkeeping: macros that change accounting data need an audit trail (GoBD); never overwrite source documents.
- Continuity: a macro without owner, documentation and test file is a risk when the author leaves.
Getting started
- Collect 5 chores in a one-hour team round (GUIDE-001); rank by minutes per month.
- Pick the simplest, tidy the sheet, anonymise 5 sample rows.
- Let the assistant generate the macro, test on a copy, paste errors back until stable.
- Second-person review; store it in the library with owner and short README.
- After four weeks count time saved, then decide on rollout and rules (GUIDE-009, GUIDE-007).
Sources
- Microsoft Learn - Office Scripts in Excel (overview, Power Automate, scheduling note) - https://learn.microsoft.com/office/dev/scripts/overview/excel
- Google for Developers - Apps Script overview - https://developers.google.com/apps-script/overview
- Microsoft Support - Use Claude with Agent Mode in Excel - https://support.microsoft.com/topic/use-claude-with-agent-mode-in-excel-b2c3b3ec-154b-484b-84d0-914a80df395a
- Microsoft Learn - Macros from the internet are blocked by default in Office - https://learn.microsoft.com/microsoft-365-apps/security/internet-macros-blocked
- GeekWire - What is vibe working: Microsoft brings agent-powered AI to Excel, Word and PowerPoint - https://geekwire.com/2025/what-is-vibe-working-microsoft-brings-agent-powered-ai-to-excel-word-and-powerpoint
- Regulation (EU) 2024/1689 (AI Act) - https://eur-lex.europa.eu/eli/reg/2024/1689/oj