35 Claude Prompts for Excel Macros and VBA
Claude cannot open your workbook or press Run, so these 35 prompts do the part it is good at: you describe the sheet or paste the broken code, and Claude returns commented VBA you drop into the Visual Basic Editor with Alt+F11. Automation, cleaning macros, error handling, and migration plans included.
In short: This page contains 35 copy-paste ready prompts, organized into 5 categories with a description and pro tip for each. The first 5 prompts are free instantly, no signup needed. Hand-curated and tested by the AI Academy team.
VBA Macro Generation
7 promptsMacro From a Plain-English Spec
1/35✨ What it does
Claude writes a commented VBA macro that does [describe the outcome in plain English] on sheet [name], matched to your [365 / 2019 / 2016] setup. You run it on a copy of the file and only then point it at the live sheet.
<context>Workbook layout: sheet [name] has headers in row [n] and columns [list what each column holds]. Data runs to roughly [rows] rows and grows every [period]. Excel version: [365 / 2019 / 2016], Windows or Mac: [which].</context> <task>Write a VBA macro that does exactly this: [describe the outcome in plain English].</task> <format>Complete Sub with Option Explicit, declared variables, comments on every block, no Select or Activate, and a short note at the end listing the assumptions you made about my data.</format>
Pro tip: The assumptions note is where the bugs hide. If Claude assumed headers in row 1 and yours are in row 3, you find out before you run it on real data.
Clean Up a Recorded Macro
2/35✨ What it does
Claude rewrites the recorded macro so it stops selecting cells, names sheets and ranges, and uses loops instead of repeated clicks. You replace the old procedure, then run it once with the wrong sheet active to prove it holds.
<context>[Paste the macro the recorder produced.] It works but it is slow, it flickers, and it breaks whenever the active sheet is wrong.</context> <task>Rewrite it properly: remove every Select and Activate, reference sheets and ranges explicitly, declare variables, and collapse the repeated blocks into a loop.</task> <format>The rewritten macro first, then a two column list of what you changed and why it matters, so I learn the pattern instead of just pasting it.</format>
Pro tip: The recorder is still the fastest way to discover the object names for an unfamiliar feature. Record it, then hand the result to Claude to make it real code.
Safe Loop Over Every Sheet
3/35✨ What it does
Claude writes VBA that loops your [N] sheets and applies [the action] only to tabs that match [name pattern / tab color / a value in cell A1]. You test it on a dummy workbook that still has lookup sheets you must not touch.
<context>My workbook has [N] sheets. Some are data sheets, some are lookups or settings I must not touch. The ones to process are identified by [name pattern / tab color / a value in cell A1].</context> <task>Write VBA that loops through the workbook and applies [the action] only to qualifying sheets.</task> <format>Full macro with the exclusion rule as an editable list at the top, a counter that reports how many sheets were processed, and handling for hidden and very hidden sheets.</format>
Pro tip: Always exclude by an explicit allow list rather than by index. Someone will reorder the tabs, and index based loops fail silently when they do.
Merge Every Workbook in a Folder
4/35✨ What it does
Claude writes a macro that stacks every same-layout file in [path] onto one master sheet and adds a column for the source file name. You point it at a folder of copies first, then keep the master.
<context>A folder at [path] receives [N] files per [period], each with the same layout: headers in row [n], data on sheet [name]. I need them stacked into one master sheet with a column showing the source file name.</context> <task>Write a VBA macro that opens each file, copies the used range below the existing data, and closes it without saving.</task> <format>Full macro with the folder path as a variable, a skip for files already imported, screen updating turned off during the run, and an error path that logs any file it could not open.</format>
Pro tip: Ask for the source file name column even if you do not think you need it. When a number looks wrong three weeks later, it is the only way to trace it back.
Build a Formatted Report Sheet
5/35✨ What it does
Claude writes VBA that rebuilds the monthly formatted report from sheet [name], including totals and layout, and replaces last month's sheet cleanly. You compare one generated report to a handmade one before you trust it.
<context>Raw data lives on sheet [name] with columns [list]. Every month I rebuild the same formatted report by hand: [describe the formatting, totals, and layout].</context> <task>Write VBA that generates the report sheet from the raw data, replacing it cleanly if it already exists.</task> <format>Full macro covering headers, number formats, column widths, freeze panes, borders, a totals row, and the print area, with each formatting block in its own commented section I can adjust.</format>
Pro tip: Have it delete and recreate the report sheet rather than overwriting cells. Overwriting leaves last month's stray rows sitting at the bottom of the range.
Data Entry Form With Validation
6/35✨ What it does
Claude builds a UserForm that collects [list them with their types and which are mandatory], checks the values, and appends a clean row. You try bad dates and blank required fields, then give the form to the people who type on the grid today.
<context>People enter [type of record] straight onto the sheet and keep breaking it: wrong columns, text in date fields, blank required values. The fields are [list them with their types and which are mandatory].</context> <task>Write a VBA UserForm that collects those fields, validates them, and appends a clean row to the table.</task> <format>The form control layout, the full code behind it, validation rules per field with the message shown on failure, and instructions for adding the form and wiring a button to open it.</format>
Pro tip: Validate before writing anything, not field by field as they tab through. Half written rows from a cancelled entry are worse than the messy typing you were trying to fix.
Rebuild and Refresh a PivotTable
7/35✨ What it does
Claude writes VBA that points the pivot at the growing range on sheet [name] and rebuilds the rows, columns, values, and filters you named. You refresh after adding new rows and check that the totals still match the source.
<context>Source data is on sheet [name] and grows every [period]. The pivot I rebuild manually has rows [fields], columns [fields], values [fields with their aggregation], and filters [fields].</context> <task>Write VBA that points the pivot cache at the current data range, rebuilds the pivot with that layout, and refreshes it.</task> <format>Full macro that handles the pivot not existing yet, uses a dynamic range or table reference rather than a fixed one, and explains the errors that appear when a field name changes in the source.</format>
Pro tip: Convert the source to a real Excel table with Ctrl+T first. The pivot then follows new rows automatically and half of the VBA becomes unnecessary.
XML tags are just the start. Learn the full Claude workflow.
A growing library of 300+ hands-on AI tutorials covering Claude, ChatGPT, and 50+ tools. New tutorials added every week.
Automating Repetitive Tasks
7 promptsOne-Click Monthly Reporting Macro
8/35✨ What it does
Claude collapses [list the steps in order] into one macro and tells you which steps still need a human. You keep those decision steps manual and run the rest with one button.
<context>Every month I do the same sequence by hand: [list the steps in order, from opening the file to sending the output]. It takes about [duration].</context> <task>Write a single VBA macro that runs the whole sequence, and tell me which steps should stay manual because they need a human decision.</task> <format>One master Sub calling one Sub per step so I can run them individually while testing, plus a message box at the end summarizing what ran and what it produced.</format>
Pro tip: Insert a shape on the sheet, right click it and choose Assign macro. A labelled button beats teaching everyone the Alt+F8 dialog.
Split a Master Sheet Into One File Per Value
9/35✨ What it does
Claude writes VBA that splits sheet [name] into one workbook per value in column [letter] and saves each file to [folder] with the header intact. You open two of the output files and confirm nobody else's rows slipped in.
<context>Sheet [name] holds all records with headers in row [n]. Column [letter] contains [the split key, for example region or sales rep], with roughly [count] distinct values.</context> <task>Write VBA that creates one workbook per distinct value, containing only that value's rows plus the header, saved to [folder] with a file name built from [pattern].</task> <format>Full macro with a unique value list built at run time, formatting carried over to each file, files closed after saving, and a summary of file names and row counts written to a log sheet.</format>
Pro tip: Check the row counts in the log add up to the master total before you send anything. A filter that silently excludes blanks is the classic way records go missing.
Batch Export Sheets to PDF
10/35✨ What it does
Claude writes VBA that exports [which sheets] to named PDFs in [folder], plus an option to combine selected sheets into one file. You check the file names against [describe the naming pattern] and open one PDF before you send them.
<context>I export [which sheets] to PDF every [period] and name each file by hand as [describe the naming pattern, including any date component]. They go to [folder].</context> <task>Write VBA that exports each sheet as its own PDF with the right name, plus an option to export selected sheets into a single combined PDF.</task> <format>Full macro with the naming pattern in one function I can edit, page setup applied before export, a check that the folder exists, and confirmation of whether it overwrites or skips existing files.</format>
Pro tip: Set the print area and fit to width inside the macro before exporting. Otherwise a report that looked fine on screen arrives split across five pages.
Personalized Emails Through Outlook
11/35✨ What it does
Claude writes VBA that builds one Outlook message per row from [email, name, and the fields that vary per message] using your template. You run it in review mode, read three drafts, then switch to send.
<context>Sheet [name] holds one recipient per row with columns [email, name, and the fields that vary per message]. I use desktop Outlook on Windows. The message says [paste the template with placeholders].</context> <task>Write VBA that builds a personalized email per row and either displays it for review or sends it, controlled by a flag.</task> <format>Full macro using late binding so it runs without setting a reference, a DISPLAY_ONLY flag defaulting to true, attachment support from a file path column, and a sent status written back to the sheet.</format>
Pro tip: Keep DISPLAY_ONLY true for the first three rows and read the drafts. Sending mode with a bad merge field means everyone gets an email addressed to Dear [name].
Run Automatically on Open or Save
12/35✨ What it does
Claude writes the event handler that runs [describe the action] on [open / before save / when a specific cell changes] and tells you where the code must live. You paste it in the workbook module, save, reopen, and confirm the side effect.
<context>I want [describe the action, for example refresh the queries, hide the working sheets, stamp the last opened date] to happen without anyone remembering to trigger it, on [open / before save / when a specific cell changes].</context> <task>Write the correct event handler for that trigger and explain where the code has to live.</task> <format>The code with the exact module it belongs in, ThisWorkbook or the sheet module, a guard against the handler firing recursively, and a note on what happens when the user disables macros.</format>
Pro tip: Any handler that writes to cells must set Application.EnableEvents to False first and restore it in the cleanup. Without that, a Change event can trigger itself forever.
Clone a Template Sheet for the New Period
13/35✨ What it does
Claude writes a macro that copies sheet [template name], renames it to [naming pattern], clears inputs, updates the period label, and refuses to run twice for the same period. You run it once, try a second run, and check the formula links.
<context>Each [week / month] I copy sheet [template name], rename it to [naming pattern], clear the input cells, and update the period label in [cell]. The formulas link to [describe the links].</context> <task>Write VBA that does the whole thing and refuses to run twice for the same period.</task> <format>Full macro that copies the template to the correct tab position, applies the name, clears only the input ranges, updates the label, and shows a clear message if the sheet for that period already exists.</format>
Pro tip: Clear inputs by named range, never by hardcoded addresses. Named ranges survive somebody inserting a column, and cell addresses do not.
Standardize Page Setup Across Sheets
14/35✨ What it does
Claude writes VBA that applies one print setup ([orientation, margins, header and footer content, fit to page rules, repeat title rows]) to every sheet or a named subset. You print-preview two sheets and fix any header that still looks wrong.
<context>Sheets in this workbook print inconsistently: different orientations, margins, headers, and scaling. The standard should be [orientation, margins, header and footer content, fit to page rules, repeat title rows].</context> <task>Write VBA that applies that page setup to every sheet, or to a named subset.</task> <format>Full macro with the settings grouped at the top as constants, screen updating disabled because page setup is slow, and a note on which settings behave differently on Mac.</format>
Pro tip: Page setup is one of the slowest calls in VBA. Turning off screen updating and setting the printer once at the start can cut a long run to a few seconds.
Data Cleaning Macros
7 promptsWhitespace, Case and Character Normalizer
15/35✨ What it does
Claude writes a macro that strips extra spaces, odd characters, and line breaks from columns [list] and applies the case you want on each. You run it on a copy, then spot-check a few cells against the source export.
<context>Sheet [name] receives data from [source] and arrives dirty: trailing spaces, non breaking spaces, inconsistent capitalization, and stray line breaks inside cells. The affected columns are [list] and each should end up as [desired case per column].</context> <task>Write a VBA macro that normalizes those columns in place.</task> <format>Full macro that reads the range into an array, cleans it in memory, and writes it back in one operation, with each cleaning rule as its own function so I can disable individual rules.</format>
Pro tip: The array round trip matters. Cleaning 50,000 cells one at a time takes minutes, while reading into an array and writing back once takes under a second.
Remove Duplicates With an Audit Log
16/35✨ What it does
Claude writes VBA that deletes duplicates by [which columns define identity], keeps the row that matches [the rule], and logs every deleted row. You read the log, then restore one row to prove you can undo a bad keep rule.
<context>Sheet [name] has [rows] records. A duplicate means the same [which columns define identity]. When two rows match, keep the one with [the rule, for example the most recent date or the most complete row].</context> <task>Write VBA that removes duplicates by that rule and records what it deleted.</task> <format>Full macro that writes every removed row to a Removed sheet with the reason and the row it lost to, plus a summary of rows before, rows after, and rows deleted.</format>
Pro tip: Never dedupe without the audit sheet. The rule that looked obvious usually deletes something a colleague cared about, and the log is what lets you put it back.
Convert Text Numbers and Dates to Real Values
17/35✨ What it does
Claude writes VBA that turns text numbers and [format] dates in columns [list] into real values, including trailing minus signs, and lists what it could not convert. You sort the leftovers and fix those cells by hand.
<context>Columns [list] came from an export and are stored as text: numbers with thousands separators or trailing spaces, dates in [format], and some negatives written with a trailing minus or in parentheses.</context> <task>Write VBA that converts each column to a genuine numeric or date value and reports anything it could not convert.</task> <format>Full macro with a per column type mapping at the top, correct handling of the regional date order, cells it failed on highlighted, and their addresses listed on a report sheet.</format>
Pro tip: Tell Claude your regional settings. A date like 03/04/2026 converts to two completely different days depending on whether the machine expects day first or month first.
Standardize Labels Against a Mapping Table
18/35✨ What it does
Claude writes VBA that rewrites column [letter] using your mapping sheet and collects every label that has no match. You add the missing pairs to the map and run it again until the leftover list is empty.
<context>Column [letter] holds category labels typed by humans, so the same thing appears as [give three real variants]. Sheet [name] holds my mapping table with a raw value column and a clean value column.</context> <task>Write VBA that rewrites the column using the mapping, and collects every value that has no mapping entry.</task> <format>Full macro loading the mapping into a Dictionary for speed, matching case insensitively and ignoring surrounding spaces, and writing unmapped values with their counts to a review sheet.</format>
Pro tip: Ask for the unmapped list sorted by frequency. Fixing the top ten entries usually cleans most of the file, and the long tail can wait.
Quarantine Invalid Rows to a Review Sheet
19/35✨ What it does
Claude writes VBA that moves rows failing [state the rules] to a Quarantine sheet and writes the reason on each row. You scan Quarantine, fix what you can, and only then refresh the downstream reports.
<context>A valid row on sheet [name] means [state the rules, for example required fields present, amount greater than zero, date within the period, ID matching the pattern]. Downstream reports break when bad rows slip through.</context> <task>Write VBA that moves failing rows to a Quarantine sheet with the reason attached, leaving only clean rows behind.</task> <format>Full macro with the validation rules as separate functions, one reason column per failed rule, a count per rule at the end, and a companion macro that restores a corrected row to the main sheet.</format>
Pro tip: Run this before every downstream refresh, not just after something breaks. A quarantine sheet with three rows is a five minute fix, one with three hundred is a project.
Unmerge Cells and Fill Down Headers
20/35✨ What it does
Claude writes VBA that unmerges the pretty [system] export, fills values down, and strips subtotal rows so you can pivot the result. You build one pivot on the cleaned sheet and confirm the counts look sane.
<context>This sheet came out of [system] as a formatted report: merged cells in the header area, category names merged across their group of rows, blank cells that should repeat the value above, and subtotal rows scattered through the data.</context> <task>Write VBA that unmerges everything, fills the values down into the blanks, and removes the subtotal rows to leave a flat table.</task> <format>Full macro operating on a copy of the sheet so the original survives, with the subtotal detection rule stated explicitly and a count of the rows it removed.</format>
Pro tip: Always have it work on a duplicate sheet. Unmerge and fill down is the single hardest cleaning step to reverse once you have saved the file.
Pattern Extraction Macro With RegExp
21/35✨ What it does
Claude writes a pattern-matching macro that pulls [what to extract] from column [letter] into a new column and flags rows with no match. You check the flags against [paste three real examples] and tighten the pattern if it misses.
<context>Column [letter] holds free text like [paste three real examples]. I need [what to extract, for example the invoice number, the domain, the amount] pulled into a new column, and rows with no match flagged.</context> <task>Write a VBA macro using the VBScript RegExp object to extract that pattern.</task> <format>Full macro with late binding so no reference has to be set, the pattern in a clearly commented constant with an explanation of each part, the extraction written to a new column, and unmatched rows highlighted.</format>
Pro tip: Ask Claude to explain the pattern piece by piece in comments. Next quarter the source format shifts slightly and you will need to edit the pattern, not rewrite it.
These prompts give you the what. Tutorials give you the why.
Learn when to use extended thinking, how to build Claude Projects, and workflows that compound. 300+ tutorials and growing.
Debugging & Error Handling VBA
7 promptsExplain and Fix a Runtime Error
22/35✨ What it does
Claude explains what [paste the number and message] means on [paste the highlighted line], then rewrites the macro so the same crash is less likely. You run the fixed code on the file that failed, not a clean sample.
<context>Here is the macro: [paste the full code]. Here is the error: [paste the number and message], and it stops on this line: [paste the highlighted line]. It happens [every run / only on certain files / only since last week].</context> <task>Explain what the error actually means in this specific code, fix it, and separate the root cause from the symptom.</task> <format>The corrected code, a plain English explanation of the cause, and a list of the other places in this macro where the same mistake will bite me later.</format>
Pro tip: Paste the whole procedure, not just the failing line. Error 1004 on a range reference is usually caused by something set fifteen lines earlier.
Add Structured Error Handling With Cleanup
23/35✨ What it does
Claude wraps [Paste the macro] so screen updating, calculation, and opened files always get restored, even when it fails halfway. You force an error on purpose and confirm Excel is still usable afterward.
<context>[Paste the macro.] It turns off screen updating, sets calculation to manual, and opens other workbooks. When it crashes halfway, Excel is left in a broken state and the user has to restart.</context> <task>Add proper error handling so it always restores the environment, whether it succeeds or fails.</task> <format>Rewritten macro using On Error GoTo with a single labelled cleanup section, the error number and description reported to the user in plain language, and an explanation of when Resume Next is acceptable and when it hides real bugs.</format>
Pro tip: Restore EnableEvents, Calculation, and ScreenUpdating in the cleanup label, not at the end of the happy path. That is exactly the code an early exit skips.
Works Here, Breaks on Their Machine
24/35✨ What it does
Claude finds why the macro runs for you and fails for [who] on [Excel version, Windows or Mac, 32 or 64 bit], including missing references and 64-bit declares. You send them the revised file and stay on the call for the first run.
<context>[Paste the macro.] It runs on my machine and fails for [who] with [paste their error]. They are on [Excel version, Windows or Mac, 32 or 64 bit], I am on [mine], and the file lives on [OneDrive / a network share / local disk].</context> <task>Find every portability problem: missing references, API declarations without PtrSafe, hardcoded paths, regional separators, and features absent on their build.</task> <format>A table of issue, why it only breaks for them, and the fix, then the corrected macro using late binding wherever it avoids a reference.</format>
Pro tip: Late binding removes most of these problems at once. Declaring objects As Object with CreateObject means no reference to set and no version mismatch to chase.
Speed Up a Slow Macro
25/35✨ What it does
Claude rewrites the slow macro to read and write through arrays, drop cell-by-cell loops, and stop the screen flicker on [rows] rows. You time it on the real file and keep the old copy until the totals match.
<context>[Paste the macro.] It runs for about [duration] on roughly [rows] rows, and the screen flickers the entire time.</context> <task>Profile it in your head and make it fast: batch the range reads and writes through arrays, kill the cell by cell loops, and manage the application state properly.</task> <format>The optimized macro, a before and after list of each change with the expected impact, and a note on which change gives the biggest gain so I understand where the time actually went.</format>
Pro tip: Almost all VBA slowness is round trips to the sheet. One read into an array, all the work in memory, one write back, and a two minute macro often lands under five seconds.
Add Logging and a Debug Plan
26/35✨ What it does
Claude adds a log of each major step, key values, and the outcome, plus a plan for narrowing the next intermittent failure. You collect one log from the person who hits the bug and send that back, not a guess.
<context>[Paste the macro.] It fails intermittently for other users and I cannot reproduce it, so I need evidence rather than guesses.</context> <task>Add a logging routine that records each major step, key variable values, and the outcome, and give me a plan for narrowing down the failure.</task> <format>The instrumented macro writing to a Log sheet with timestamp, step, and value, a LOGGING flag to switch it off, and a short debugging plan covering breakpoints, the Immediate window, and the Locals window.</format>
Pro tip: Log to a worksheet, not to Debug.Print. The Immediate window is empty by the time a colleague sends you a screenshot of the error.
Explain an Inherited Macro Line by Line
27/35✨ What it does
Claude walks through the inherited macro block by block and lists exactly what it changes, deletes, or sends outside the workbook. You read that list, then run it on a copy before you touch the live file.
<context>[Paste the macro I inherited.] Nobody here wrote it, nobody knows exactly what it touches, and we are afraid to run it on the live workbook.</context> <task>Explain what it does, block by block, and tell me exactly what it modifies, deletes, or sends outside the workbook.</task> <format>A walkthrough per block in plain English, a list of every side effect grouped as safe, destructive, or external, and the questions I should answer about my data before running it once.</format>
Pro tip: Read the destructive list before you test. Any macro containing Delete, Kill, or SaveAs deserves a copy of the workbook and a copy of the folder first.
Make a Macro Safe to Re-Run
28/35✨ What it does
Claude makes the macro safe to run twice so a second click no longer [duplicates rows / doubles the totals / fails because the sheet already exists], and shows a clear message instead. You click it three times on a copy and check the totals.
<context>[Paste the macro.] Running it twice [duplicates rows / doubles the totals / fails because the sheet already exists], and somebody always runs it twice.</context> <task>Make it idempotent: the same result whether it runs once or five times, with a clear message rather than an error on a repeat run.</task> <format>The rewritten macro with the repeat detection explained, an optional timestamped backup of the affected sheet before it changes anything, and a note on what genuinely cannot be undone.</format>
Pro tip: VBA changes do not go into the Excel undo stack. A backup copy of the sheet at the start of the run is the only undo your users will ever have.
Converting Manual Workflows to Macros
7 promptsTurn a Written SOP Into a Macro Plan
29/35✨ What it does
Claude turns [Paste the SOP or the step by step instructions people follow today] into an automation plan: mechanical steps, human decisions, and parts too fragile to code. You approve the plan before anyone writes VBA.
<context>[Paste the SOP or the step by step instructions people follow today.] It runs [frequency], takes [duration], and is done by [role] in Excel [version].</context> <task>Before writing any code, turn it into an automation plan: which steps are mechanical, which need a human decision, and which are too fragile to automate safely.</task> <format>A table of step, automate or keep manual, the VBA approach in one line, and the risk if it goes wrong unattended. End with the order I should build the pieces in.</format>
Pro tip: Build in the order the plan gives you and stop after each piece. A half automated process that people trust beats a full one that nobody dares run.
Choose VBA, Power Query or Office Scripts
30/35✨ What it does
Claude recommends VBA, Power Query, or Office Scripts for [describe it] based on [Excel desktop on Windows / Excel on the web / Mac] and your macro policy. You pick the tool it argues for and start only that prototype.
<context>The task: [describe it]. It runs [frequency], on [where the data comes from], and the output goes to [destination]. The people running it use [Excel desktop on Windows / Excel on the web / Mac], and IT policy on macros is [describe it].</context> <task>Recommend the right tool and justify it against the alternatives.</task> <format>A recommendation with the reasoning, a short table scoring VBA, Power Query, Office Scripts, and Power Automate on fit, maintenance, and portability, and the specific reason each rejected option loses here.</format>
Pro tip: If the task is load, reshape, and refresh, Power Query wins almost every time. Reach for VBA when you need to drive Excel itself, other Office apps, or the file system.
Replace Manual Copy-Paste Between Workbooks
31/35✨ What it does
Claude writes a macro that copies [which range or columns] from [source file] into [destination file] and does the cleanup you now do by hand. You run it on last period's pair of files and compare the result to your old paste.
<context>Every [period] I open [source file], copy [which range or columns], and paste into [destination file] at [where], then adjust [what I fix by hand]. Both files live in [location] and the source layout changes when [describe].</context> <task>Write a VBA macro that does the transfer, including the manual adjustments.</task> <format>Full macro locating columns by header name rather than by position, values pasted without formatting unless I say otherwise, a check that the source file exists and is not already open, and a summary of rows transferred.</format>
Pro tip: Matching columns by header text is what stops the macro breaking. Positional references die the first time somebody inserts a column into the source export.
Parameterize a Hardcoded Macro
32/35✨ What it does
Claude lifts paths, sheet names, dates, and thresholds out of [Paste the macro] onto a settings sheet a non-coder can edit. You change one setting and rerun, without opening the code.
<context>[Paste the macro.] It has the file paths, sheet names, date range, and thresholds written directly into the code, so I edit the code every time something changes.</context> <task>Lift every one of those into configuration a non coder can change safely.</task> <format>The rewritten macro reading its settings from a Settings sheet with labelled named ranges, validation that each setting exists and is the right type before the run starts, and the exact Settings sheet layout to build.</format>
Pro tip: Validate the settings before the macro touches any data. Discovering that a date cell is blank halfway through a 20,000 row run is how you end up with half processed files.
Migration Plan From Legacy VBA
33/35✨ What it does
Claude maps the legacy macro onto Office Scripts, Power Query, Power Automate, or leftover VBA, and migrates one piece as proof before [timeline]. You implement that one piece first and keep the old macro as fallback.
<context>[Paste the legacy macro or describe what it does.] It has to keep working when the team moves to [Excel on the web / Mac / a locked down macro policy] by [timeline].</context> <task>Map it to a modern equivalent: what becomes Office Scripts, what becomes Power Query, what becomes a Power Automate flow, and what genuinely has no replacement.</task> <format>A component by component table with the VBA feature, the modern equivalent, and the gap. Then the TypeScript for the single most important piece, so I can prove the migration works.</format>
Pro tip: Migrate the piece that runs most often first, not the biggest one. An early win on a weekly task buys you the time to deal with the parts that have no clean equivalent.
Package Macros for the Whole Team
34/35✨ What it does
Claude compares a personal macro workbook, a shared add-in, and other install paths for your [N] macros across [Windows, Excel version]. You pick the path IT will allow and package one macro that way first.
<context>I have [N] macros that [describe what they do] and [count] colleagues need them across many different workbooks. Our environment is [Windows, Excel version, whether IT signs code, where shared files live].</context> <task>Recommend how to distribute them, comparing the personal macro workbook, a shared add-in, and a template file, then give me the steps for your recommendation.</task> <format>The comparison in a short table, then numbered setup steps covering file placement, trusted locations, code signing if relevant, and how I push an update without visiting each desk.</format>
Pro tip: An .xlam add-in in a shared trusted location is usually the answer. One file to update, and the macros follow the user into every workbook they open.
Runbook and Handover Doc for a Macro
35/35✨ What it does
Claude writes a runbook for [role]: what the macro does, what must be true first, how to run it, what success looks like, and how to recover. You watch them run it once from the runbook and fix any missing step.
<context>[Paste the finished macro.] I am handing it to [role] who does not write code, and I want to stop being the only person who can run or repair it.</context> <task>Write the runbook that ships with it.</task> <format>What it does in one paragraph, prerequisites, how to run it step by step, what a successful run looks like, the three most likely errors with what to do for each, what to check afterwards, and who to escalate to. Plain language, no jargon.</format>
Pro tip: Include what a successful run looks like, with real numbers. Without it, nobody can tell the difference between a macro that worked and one that silently did nothing.
Free tool
Formula Genie
Describe what you need and get the exact Excel or Google Sheets formula.
Frequently Asked Questions
Prompts are the starting line. Tutorials are the finish.
A growing library of 300+ hands-on tutorials on ChatGPT, Claude, Midjourney, and 50+ AI tools. New tutorials added every week.
7-day free trial. Cancel anytime.
Related guides