35 Claude Prompts for Excel Macros and VBA

<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>

<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>

<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>

<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>

<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>

<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>

<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>

<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>

<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>

<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>

<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>

<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>

<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>

<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>

<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>

<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>

<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>

<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>

<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>

<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>

<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>

<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>