How to Write Excel VBA Macros with AI

How to Write Excel VBA Macros with AI (ChatGPT, Claude, Gemini and Copilot)

By Ajay Anand, Microsoft Excel MVP · Last updated:

To write a VBA macro with AI, describe the task in a chat with ChatGPT, Claude, Gemini or Copilot, paste the code into a module in the VBA editor (Alt + F11), save the file as .xlsm and run the macro with Alt + F8. The AI add-ins that work inside Excel can’t create or run VBA themselves, so the chat is the right place for macros. Always test a new macro on a copy of your file first.

Which AI tools can write VBA macros?

All the main AI assistants can write VBA in a normal chat, including on their free plans. None of the Excel add-ins insert VBA into your workbook: ChatGPT for Excel, Claude for Excel and Copilot’s editing mode change your sheet with Excel’s own features (and Python, in Copilot’s case), not with macros.

ToolWrites VBA in chatAdds VBA inside ExcelFree plan
ChatGPTYesNo; the add-in doesn’t fully support macrosYes
ClaudeYesNo; the add-in can’t run macros or VBAYes
GeminiYesNo (Gemini works in Google Sheets, which uses Apps Script, not VBA)Yes
Microsoft CopilotYes, in the Copilot app or chatNo; Copilot edits workbooks with Excel features and PythonCopilot app is free

How do you add an AI-written macro to Excel?

It takes six steps and about two minutes. You only need to do step 1 once.

  1. Show the Developer tab — go to File → Options → Customize Ribbon and tick Developer.
  2. Ask the AI for the macro — use the prompt template below, with your sheet name and ranges.
  3. Open the VBA editor — press Alt + F11, then choose Insert → Module.
  4. Paste the code into the empty module window.
  5. Save as a macro-enabled workbook — File → Save As and choose Excel Macro-Enabled Workbook (*.xlsm). A normal .xlsx file deletes the macro when you save.
  6. Run the macro — press Alt + F8, select the macro and click Run.
The Excel VBA editor with Module1 selected in the project pane and the HighlightAboveTarget macro pasted into the code window
The AI-written macro pasted into Module1 of the VBA editor.

Note: macros run in Excel for Windows and Mac. They don’t run in Excel for the web or on phones.

How do you write a good prompt for a macro?

A good macro prompt names the sheet, the exact ranges, what should happen, and what the macro must not touch. Ask for short code with comments, so you can read what it does before you run it.

Write an Excel VBA macro for the sheet “[sheet name]”. My data is in [B2:D100], with headers in row [2]. The macro should [what you want to happen]. Do not change [anything that must stay the same]. Keep the code short, add a comment on each step, and tell me how to run it.

Examples: three useful macros written with AI

Each example shows the prompt, the code the AI should give you, and the result. The code is short on purpose: shorter macros are easier to check.

1. Highlight values above a target

Write a VBA macro that fills every cell in D3:D12 with a light colour if its value is more than 20,000.
Sub HighlightAboveTarget()
    Dim c As Range
    For Each c In Range("D3:D12")
        If c.Value > 20000 Then c.Interior.Color = RGB(253, 241, 216)
    Next c
End Sub
Sales data in B2:D12 after running the macro: the four sales above 20,000 in D3, D6, D7 and D8 are filled with a light colour
After running the macro: the four sales above 20,000 are highlighted.

The macro checks each cell in D3:D12 one by one. For a highlight that updates by itself when values change, conditional formatting is the better tool; a macro is useful when you want a fixed result.

2. Delete blank rows

Write a VBA macro that deletes every completely empty row in the used range of the active sheet.
Sub DeleteBlankRows()
    Dim r As Long
    With ActiveSheet.UsedRange
        For r = .Row + .Rows.Count - 1 To .Row Step -1
            If WorksheetFunction.CountA(Rows(r)) = 0 Then Rows(r).Delete
        Next r
    End With
End Sub
Employee list in B2:D12 with blank rows at rows 4, 7, 8 and 11 before running the macro
Before: the list has four blank rows.
The same employee list after running the macro, with the six employees in rows 3 to 8 and no blank rows
After: the blank rows are gone and the list is continuous.

The loop runs from the bottom row up (Step -1). Deleting from the top down would skip rows, because each deletion moves the rows below it up by one. This is a common mistake in AI code, so check for it.

3. Save each sheet as a PDF

Write a VBA macro that saves every worksheet in this workbook as a separate PDF in the same folder, named after the sheet.
Sub SaveSheetsAsPDF()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        ws.ExportAsFixedFormat xlTypePDF, ThisWorkbook.Path & "\" & ws.Name & ".pdf"
    Next ws
End Sub

Save the workbook before you run this macro, because an unsaved file has no folder path. On a Mac, ask the AI for a version that uses Application.PathSeparator instead of “\”.

For more ready-made code, see my collection of Excel VBA macros.

How do you test a macro from AI safely?

A macro can’t be undone with Ctrl + Z, so test every AI-written macro on a copy of your file. Read the code first: if it deletes, clears or overwrites anything, make sure the ranges are right.

  • Work on a copy — save a backup before you run any new macro.
  • Read the ranges — check every sheet name and range in the code against your data.
  • Step through it — in the VBA editor, press F8 to run one line at a time and watch what happens.
  • Ask the AI to explain — paste the code back and ask what each line does.
  • Don’t run macros you don’t understand from files or websites you don’t trust.

What errors do AI-written macros cause?

Most errors come from a mismatch between the code and your workbook, not from the AI’s logic. Paste the exact error message back into the chat, and the AI can usually fix it.

ErrorUsual causeFix
Subscript out of rangeThe sheet name in the code doesn’t match your sheetCorrect the sheet name, including spaces
Compile error: Sub or Function not definedA typo, or a function that doesn’t exist in VBAPaste the error into the chat and ask for a fix
Run-time error 1004The code refers to a range or file that doesn’t existCheck the ranges and file paths
Macro disappears after savingThe file was saved as .xlsxSave as .xlsm
Macros are blockedThe file came from the internet or emailRight-click the file → Properties → tick Unblock, if you trust it

FAQ

Can ChatGPT write VBA code for Excel?

Yes. ChatGPT can write, explain and fix VBA macros in a chat, including on the free plan. Paste the code into a module in the VBA editor to use it.

Can Copilot write macros inside Excel?

Copilot can write VBA code when you ask in chat, but its editing mode in Excel changes your workbook with Excel features and Python, not with VBA. Paste the code into the VBA editor yourself.

Is AI-written VBA code safe?

It’s as safe as the code itself. Read it, test it on a copy of your file, and don’t run code that deletes or overwrites data until you’ve checked the ranges.

Which AI is best for VBA?

ChatGPT and Claude both write reliable VBA for everyday tasks. Claude is often stronger on longer, more complex code. Whichever you use, a clear prompt with sheet names and ranges matters more than the tool.

Sources

Last updated · Written by Ajay Anand, Microsoft Excel MVP