Good ChatGPT prompts for VBA say which Office app you're in, describe your sheet or document layout, and spell out exactly what the macro should do, then ask for comments in the code. One thing to know first: Microsoft now blocks VBA macros by default in files from the internet, so a macro-enabled file you email or download may not run until it's unblocked.
VBA is still the quickest way to automate the repetitive bits of Excel, Word and Outlook on Windows, and ChatGPT has made it far less intimidating. Describe the job, and it'll write the macro, explain each line and fix it when it errors. The prompts below give it the details it needs about your file, and keep safety in view, because macros are also one of the oldest tricks attackers use.
Microsoft's macro rules you need to know
From Microsoft's own documentation on macros from the internet:
| What Microsoft says | What it means for you |
|---|---|
| Office blocks VBA macros by default in files from the internet, such as email attachments, and shows a Security Risk banner | A macro file you send a colleague may not run for them |
| To unblock a file you trust: right-click it, choose Properties, select Unblock on the General tab | Only do this for files you know are safe |
| The change affects Office on Windows in Access, Excel, PowerPoint, Project, Publisher, Visio and Word | Mac, mobile and web versions aren't affected by this change |
| VBA macros are a common way for attackers to deploy malware and ransomware | Never enable macros in a file you weren't expecting |
The catch most people hit: the macro ChatGPT wrote works perfectly on your machine, then 'breaks' when a colleague opens the emailed file. It hasn't broken. Office has blocked it. Sharing through OneDrive and using Open in Desktop App, or unblocking a trusted file, solves it.
Prompts to write Excel macros
Describe the sheet: names, columns and where the data starts.
Copy this prompt
Write an Excel VBA macro for the sheet [sheet name], where data starts in row 2 and columns A to [letter] hold [describe]. The macro should [task]. Add comments explaining each step.
Copy this prompt
Write a macro that loops through every sheet in this workbook and copies the used range from each into a new sheet called Summary, with the sheet name in the first column.
Copy this prompt
Create a macro that removes blank rows from the active sheet without deleting rows that only look empty because of formulas.
Copy this prompt
Write VBA that highlights any row where the date in column [letter] is in the past and the status in column [letter] isn't Done.
Copy this prompt
Write a macro that saves each sheet in this workbook as a separate PDF in the same folder, named after the sheet.
Prompts for Word and Outlook macros
Same approach: describe the document or mailbox job precisely.
Copy this prompt
Write a Word VBA macro that finds every instance of [text] in the active document and makes it bold, then tells me how many it changed.
Copy this prompt
Write a Word macro that applies the Heading 2 style to every paragraph that starts with a number followed by a full stop.
Copy this prompt
Write an Outlook VBA macro that saves attachments from selected emails into a folder I choose, skipping signature images.
Copy this prompt
Write a macro that inserts today's date and my standard header text at the top of a new Word document.
Prompts to fix and understand VBA
For inherited macros and error messages.
Copy this prompt
This macro stops with the error [paste error] on the line [paste line]. Explain why and give me a corrected version: [paste code].
Copy this prompt
Explain this macro line by line in plain English and tell me what it changes in my file: [paste code].
Work with meWant AI doing the heavy lifting in your marketing?
I build the systems that handle the boring 80 percent, so you get your week back. Done properly, with the human kept in.
Copy this prompt
This macro is very slow. Suggest changes, such as turning off screen updating, and explain why each one helps: [paste code].
Copy this prompt
Add error handling to this macro so it shows a clear message instead of crashing, and restores settings it changed: [paste code].
Prompts for safer macros
Build the safety in, especially for macros that delete or overwrite.
Copy this prompt
Before this macro deletes anything, add a confirmation message that says how many rows will be removed and lets me cancel.
Copy this prompt
Change this macro so it works on a copy of the sheet rather than the original: [paste code].
Copy this prompt
Review this macro I found online and tell me everything it does, including anything that connects to the internet, runs other programs or changes files outside the workbook: [paste code].
Copy this prompt
Explain the safest way to share a macro-enabled workbook with my team, given that Office blocks macros in files from the internet.
Prompts for learning VBA
If you want to understand it, not just use it.
Copy this prompt
Teach me the basics of VBA in Excel with five tiny examples I can run, each building on the last.
Copy this prompt
Explain the difference between a Sub and a Function in VBA with a simple example of each.
Copy this prompt
Give me three beginner VBA exercises based on a simple sample sheet you describe, such as a task list, with solutions at the end.
How to get better VBA from ChatGPT
- Say which app and describe the layout: sheet names, columns, header row and where data starts.
- Ask for comments in the code so you can follow and edit it later.
- Test on a copy of your file first, especially for macros that delete or overwrite.
- Ask ChatGPT to explain any macro you didn't write before you run it.
- Remember the internet block: unblock only files you trust, or share through OneDrive's Open in Desktop App.
If you'd rather avoid macros, my guide to Claude for Excel shows another way to automate spreadsheet work, and Microsoft's Copilot now handles some of these jobs inside Office. My office productivity tips cover the quick wins that need no code at all.
My advice on ChatGPT prompts for VBA
Let ChatGPT write and explain your macros, but stay in charge of what runs on your machine. Describe your file precisely, ask for comments, test on a copy and add confirmation steps to anything destructive. And respect Microsoft's macro block rather than fighting it: it exists because macros are a favourite way into a business. Do that, and a few lines of VBA can save you hours every month.
Frequently asked questions
Can ChatGPT write VBA macros?
Yes. Tell it which Office app you're using, describe the file layout and the task, and ask for comments. Test the macro on a copy first.
Why won't my macro run in a downloaded file?
Microsoft says Office blocks VBA macros by default in files from the internet, such as email attachments, and shows a Security Risk banner.
How do I unblock macros in a file I trust?
Microsoft says to right-click the file, choose Properties and select the Unblock checkbox on the General tab. Only do this for files you trust.
Does the macro block affect Office on a Mac?
No. Microsoft says the change affects Office on Windows only, and doesn't affect Office on a Mac, mobile devices or the web.
Is it safe to run VBA code from ChatGPT?
Usually, if you understand it. Ask ChatGPT to explain the macro first, test it on a copy, and never run code that touches files or the internet unexpectedly.
