ChatGPT Prompts for Excel Macros and VBA
20 copy-paste ChatGPT prompts for Excel macros: VBA code, automation patterns, debugging, modern alternatives (Office Scripts, Power Query), and the macro work that legacy enterprise still depends on.
In short: This page contains 16 copy-paste ready prompts, organized into 4 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 Code Generation
4 promptsMacro from Description
1/16✨ What it does
ChatGPT writes a complete VBA macro for [describe what it should do] in workbook context [sheets, data structure], with comments, where to paste it, variable notes, and error handling. Paste it into a copy of the workbook and run it there before you touch the original.
Generate VBA macro for: [describe what it should do]. Workbook context: [sheets, data structure]. Output: complete VBA code with comments, where to paste (workbook vs sheet vs module), variables explained, error handling. Test on copy first.
Pro tip: AI VBA can have errors (deprecated methods, wrong syntax). Always test on copy of workbook. Inherit from existing macros where possible (consistent style).
Macro Recorder Cleanup
2/16✨ What it does
ChatGPT cleans [Paste recorded macro] by removing Select and Activate, using direct ranges, simplifying, adding error handling, and commenting what it does. Replace the recorded version on a copy and confirm the result matches what you had.
[Paste recorded macro]. Cleanup: remove select/activate operations (slow + brittle), use direct ranges, simplify formulas, add error handling, comment what it does. Recorded macros work but bloated.
Pro tip: Macro recorder produces functional but slow + ugly code. Cleanup pass = 5-10x faster + maintainable. Most macros never cleaned up.
Loop Through Cells/Sheets
3/16✨ What it does
ChatGPT writes a VBA loop through [cells / sheets / workbooks] that will [action], with no Select, ScreenUpdating off, per-iteration errors, and a completion notice. Run it on a small range first, then keep ScreenUpdating off only while you test.
VBA to loop through [cells / sheets / workbooks] and [action]. Output: efficient loop pattern (avoid select), variable for performance (Application.ScreenUpdating = False), error handling per iteration, completion notification.
Pro tip: Application.ScreenUpdating = False during loops = 10x speedup. Most macros omit. Set once + restore at end.
Pivot Table Manipulation
4/16✨ What it does
ChatGPT writes VBA to [create / refresh / modify] a pivot table from [describe data], plus common errors around changing ranges and field names, and how to handle pivot caches. Run it against a copy of the source and refresh once to confirm your cache.
VBA to [create / refresh / modify] pivot table. Source: [describe data]. Output: VBA code, common errors (data range changes, field names case-sensitive), how to handle pivot caches.
Pro tip: Pivot table VBA breaks when source changes. Defensive code (check field exists, handle missing) = robust. Default code = breaks first time data structure shifts.
Prompts get you started. Tutorials level you up.
A growing library of 300+ hands-on AI tutorials. New tutorials added every week.
Automation Patterns
4 promptsWorkbook Open Automation
5/16✨ What it does
ChatGPT writes a Workbook_Open routine for [scenario] in ThisWorkbook, covering common patterns and the risk of running code from an untrusted file. Test it on a copy and only enable macros on workbooks you trust.
Workbook_Open event for [scenario]. Output: code in ThisWorkbook, common patterns (set sheet, refresh data, prompt user, validate), security considerations (don't trust workbook source). Auto-runs every time opened.
Pro tip: Workbook_Open = automatic on every open. Useful for setup/refresh; risky for security (malicious macros use). Macro security warning users see is for this reason.
User Form Design
6/16✨ What it does
ChatGPT designs a VBA UserForm for [purpose], with layout, controls, validation, OK and Cancel, and writing results back to the sheet, and notes that an Office Add-in is the longer-term path. Build the form in the editor and try a cancel so nothing writes to your sheet.
UserForm in VBA for [purpose]. Output: form layout, controls (buttons, textboxes, dropdowns, listboxes), validation logic, OK/Cancel handling, return data to spreadsheet. Modern alternative: Office Add-in (better long-term).
Pro tip: UserForms work but feel dated. For new development, Office Add-ins (HTML/JS) modern + cross-platform. Legacy = UserForms.
Email from Excel via Outlook
7/16✨ What it does
ChatGPT writes VBA that sends personalized Outlook mail from Excel, with columns for recipient, subject, and body, attachments, errors, and send versus display for review. Use display first on a short list so you can read each message before it goes.
VBA to send personalized emails from Excel via Outlook. Output: data structure (recipient, subject, body columns), Outlook automation code, attachment handling, error handling, send-vs-display option for review. Mass email patterns.
Pro tip: Display-mode (vs Send) = review before sending. Test 1 email manually before bulk. Sender reputation hit if 100s of bad emails go out from VBA bug.
Database Connection from Excel
8/16✨ What it does
ChatGPT writes VBA to query [database type] from Excel, with a connection string template, results on a sheet, errors, and cleanup, and notes Power Query is usually better. Test against a read-only account and close the connection after your pull.
VBA to query [database type — SQL Server / Access / etc.] from Excel. Output: connection string template, query execution, results into worksheet, error handling, connection cleanup. Power Query usually better; VBA legacy.
Pro tip: Power Query > VBA for new database connections (more reliable, refreshable, versioned). VBA when supporting legacy or specific automation. Don't default to VBA.
Debugging + Performance
4 promptsVBA Debugging
9/16✨ What it does
ChatGPT debugs [Paste VBA error message + code] by explaining the error, likely causes, a fix, and how to prevent it. Apply the fix on a copy and step through until the error is gone from your run.
[Paste VBA error message + code]. Debug: what error means, common causes, fix steps, prevention. Walk through code execution mentally; identify state at error. VBA debugging = state inspection.
Pro tip: Common VBA errors: object required (variable not set), type mismatch (data type wrong), subscript out of range (collection index off). Pattern recognition = faster debug.
Performance Optimization
10/16✨ What it does
ChatGPT speeds up [Paste slow VBA] with ScreenUpdating off, manual calculation, events off, no Select, batched ranges, and arrays. Time the old and new versions on the same file and keep the faster one for your work.
[Paste slow VBA]. Optimize: ScreenUpdating off, Calculation manual, EnableEvents off, avoid select, batch range operations, arrays for bulk data. 10-100x speedups common.
Pro tip: Excel calculation triggered every change in default mode. Manual calculation during macro = batched. Restore at end. Single biggest VBA speedup.
Error Handling Pattern
11/16✨ What it does
ChatGPT adds On Error GoTo handling to VBA macro [paste], with cleanup, Resume versus Exit, logging, and a readable message instead of a raw popup. Trigger a known failure and confirm the message is clear to you.
Add error handling to VBA macro [paste]. Output: On Error Goto pattern, error handler with cleanup, Resume vs Exit, logging, user-friendly error messages. Default = ugly error popups.
Pro tip: On Error Goto ErrorHandler + cleanup = professional. Default = ugly debug popups. User experience matters in macros.
Code Modernization
12/16✨ What it does
ChatGPT modernizes [Paste old VBA] by replacing deprecated calls, typing variables, adding Option Explicit, comments, and cleaner formatting so the code is safer to maintain. Compile with Option Explicit and fix every undeclared name before you run it.
[Paste old VBA]. Modernize: deprecated methods replaced, defensive coding, comments updated, variables explicit type, Option Explicit added, formatting cleaned. Old VBA still runs but unmaintainable.
Pro tip: VBA inherited from 2010 = mess of deprecated calls. Modernization pass = maintainable + faster + less brittle. Worth investment if depending on macro.
Like these prompts? There are full tutorials behind them.
Learn the workflows, not just the prompts. 300+ easy-to-follow tutorials inside AI Academy — and growing every week.
Modern Alternatives
4 promptsVBA → Power Query Migration
13/16✨ What it does
ChatGPT turns [Describe VBA macro doing data transformation] into Power Query steps, with why a refreshable query beats code and what UI or custom logic should stay in VBA. Rebuild the transform in Power Query and retire the macro once your refresh matches.
[Describe VBA macro doing data transformation]. Migrate to Power Query. Output: PQ steps, advantages (refreshable, no code, cross-platform), what to keep in VBA (UI automation, custom logic). Most VBA-for-data could be PQ.
Pro tip: Data transformation in VBA = brittle + slow. Power Query = same outcome, more robust + refreshable. Migrate when meaningful; some VBA worth replacing.
VBA → Office Scripts
14/16✨ What it does
ChatGPT turns [Describe VBA macro] into an Office Scripts TypeScript version, with key differences from VBA and when the move is worth it for Excel Online and Power Automate. Run the script on a copy in Excel Online before any flow you own calls it.
[Describe VBA macro]. Migrate to Office Scripts (TypeScript-based, runs Excel Online + desktop, Power Automate-callable). Output: TS equivalent, key differences from VBA, integration patterns, when worth migrating. Modern path.
Pro tip: Office Scripts = future. VBA = legacy. New automations = Office Scripts. Existing critical macros = migrate over time. Cross-platform + cloud-friendly.
When VBA Still Right Choice
15/16✨ What it does
ChatGPT helps you pick VBA, Office Scripts, Power Automate, Power Query, or Python via xlwings for [task], using integration, refresh frequency, platform, and complexity. Choose one path and start a small proof on a copy of your file.
I'm considering automation for [task]. Help me decide: VBA / Office Scripts / Power Automate / Power Query / Python via xlwings. Each has fit. Decision criteria: integration needs, refresh frequency, cross-platform, complexity.
Pro tip: VBA still right for: complex desktop-only Excel logic, legacy enterprise, instant local execution. Modern alternatives win for: refreshable data, cross-platform, cloud workflows.
Macro Security + Distribution
16/16✨ What it does
ChatGPT plans safe distribution of a macro-enabled workbook, covering digital signing, trusted location versus Enable Content, user warnings, an .xlam add-in, and Group Policy. Sign the file or use an add-in and tell users what warning they should expect from you.
Distribute macro-enabled workbook safely. Output: digital signing, trusted location vs explicit enable, security warnings users see, alternative: Add-in (.xlam) for distribution, deployment via Group Policy. Macro security real concern.
Pro tip: Default Excel = macros disabled (security). Signed macros = users trust automatic. Unsigned = users click through warnings (or don't). Signing matters for distribution.
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.