Prompt Library

ChatGPT Prompts for Excel Macros and VBA

20 copy-paste prompts

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.

VBA Code Generation

4 prompts

Macro from Description

1/20

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.

Generates VBA macros.

💡

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

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

Cleans up recorded VBA.

💡

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

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.

Writes VBA loops.

💡

Pro tip: Application.ScreenUpdating = False during loops = 10x speedup. Most macros omit. Set once + restore at end.

Pivot Table Manipulation

4/20

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.

Manipulates pivots via VBA.

💡

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.

Start 7-Day Free Trial

Automation Patterns

4 prompts

Workbook Open Automation

5/20

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.

Builds Workbook_Open events.

💡

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

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

Designs Excel UserForms.

💡

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

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.

Builds Excel-Outlook email VBA.

💡

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

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.

Builds VBA database connections.

💡

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 prompts

VBA Debugging

9/20

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

Debugs VBA errors.

💡

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

[Paste slow VBA]. Optimize: ScreenUpdating off, Calculation manual, EnableEvents off, avoid select, batch range operations, arrays for bulk data. 10-100x speedups common.

Optimizes VBA performance.

💡

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

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.

Adds VBA error handling.

💡

Pro tip: On Error Goto ErrorHandler + cleanup = professional. Default = ugly debug popups. User experience matters in macros.

Code Modernization

12/20

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

Modernizes legacy VBA.

💡

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.

Try AI Academy Free

Modern Alternatives

4 prompts

VBA → Power Query Migration

13/20

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

Migrates VBA to Power Query.

💡

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

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

Migrates VBA to Office Scripts.

💡

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

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.

Chooses automation tool.

💡

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

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.

Distributes macros securely.

💡

Pro tip: Default Excel = macros disabled (security). Signed macros = users trust automatic. Unsigned = users click through warnings (or don't). Signing matters for distribution.

Frequently Asked Questions

For legacy enterprise: yes, will be for years. For new development: Office Scripts, Power Automate, Python (xlwings) better. Don't learn VBA from scratch unless maintaining; new builds = modern.

VBA: desktop-only, mature, Windows-only feeling. Office Scripts: cross-platform (web + desktop), TypeScript, Power Automate-integrated, future. New automations = Office Scripts.

Often yes, sometimes hallucinates. Always test in dev workbook before production. AI as drafting tool; you verify. Hallucinated method names common; testing catches.

Trusted Locations setting + digital signature + Trust Center config. Most enterprises configure via Group Policy. Personal use = trusted location. Distribution = signing.

Built-in Immediate Window (Ctrl+G), breakpoints (F9), step-through (F8), Watch window. Master these = 10x faster debugging. Most VBA users don't use; struggle.

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.

Start 7-Day Free Trial Explore AI for Data Analysts

7-day free trial. Cancel anytime.

Browse All Prompt Collections