Can ChatGPT Write VBA Code? What AI Gets Right, What It Breaks, and How to Check It

Guide · 16 September 2026 · ~12 min read

Can ChatGPT write VBA code? Yes, in seconds, and it usually looks right. Whether it is right depends on things a chat window cannot see: your sheet names, your data, your Excel version, and what should happen when a lookup finds nothing.

Last checked: 16 September 2026. We make HISAB 360, an Excel add-in that can write VBA, so it comes last, with its limits.

The short answer

• Can ChatGPT write VBA code? Yes, as text you paste into the VBA editor. In a chat window it sees only what you paste or upload, and it cannot run the code in your Excel, so it guesses sheet names, columns and layout.
• Whichever AI tool you use, compile the code, read it and test it on a copy before it touches real numbers.

Can ChatGPT write VBA code?

Yes. ChatGPT and other chat assistants, such as Claude and Gemini, generate VBA as text. New to macros? Start with what a macro in Excel is, or see how to create a macro without writing VBA.

How to put ChatGPT's VBA into Excel, step by step

1. Save the workbook as a macro-enabled .xlsm file.
2. Press Alt+F11, then Insert > Module, and paste the code.
3. Run Debug > Compile VBAProject and fix anything it flags.
4. On a copy of the file, press Alt+F8 and run the macro.
5. To reuse a recorded macro, paste its code into the chat and ask for hard-coded ranges to become a last-row lookup.

The same works for Word or PowerPoint VBA, which have their own object models, so name the application in your prompt. But a chat window cannot look at your workbook. It does not know your data sits on a sheet called "AR Ledger", with headers in row 4 and invoice numbers stored as text. It fills those gaps with guesses, and nothing it hands you has been run against your file.

Vendor add-ins that run inside Excel are different. Anthropic's Claude for Excel documentation says it answers questions about open workbooks, but lists "Macros and VBA operations" as unsupported. OpenAI also offers a ChatGPT add-in for Excel; see our guide to ChatGPT for Excel add-in routes, and check OpenAI's documentation before relying on it for VBA.

Can Copilot write VBA code?

Microsoft's documentation, as far as we could find, does not say. The Get started with Copilot in Excel page lists editing cells and sheets, formulas, charts, PivotTables, formatting, sorting, filtering, web search, importing data and skills. On 16 September 2026 it did not mention VBA or macros. Copilot changes often, so try it yourself. See also Copilot in Excel's limits for accountants.

What is the best AI tool for writing VBA code?

There is no single winner and no independent VBA benchmark. What separates the options is how much of your workbook the tool sees, and whether anything checks the code before it runs.

Whichever you use, run the checklist below. For a wider comparison, see AI Excel tools for accountants.

Where AI-written VBA goes wrong

None of these faults is exotic; each slips through because the code reads naturally.

1. It guesses your sheet names and layout

Sheets("Sheet1"), Range("A2:A100") or headers assumed to be in row 1 are guesses. At best the macro stops with "Subscript out of range"; at worst it runs on the wrong sheet or ignores row 101 onwards.

2. It invents properties and methods

Assistants sometimes use members that sound right but do not exist, such as ws.LastRow. On a variable declared As Worksheet, Debug > Compile stops with "Method or data member not found". On ActiveSheet, an Object or a Variant, the line fails only when it runs, with error 438. Declare specific types so the compiler catches it.

3. It relies on Select, Activate and the active sheet

An unqualified Range or Cells in a standard module acts on whichever sheet is active, so values can land in the wrong place. Qualify every reference: ThisWorkbook.Worksheets("Invoices").Range("A1").

4. It forgets Set, or borrows syntax from other languages

Writing ws = Worksheets("Invoices") without Set stops with run-time error 91, which Microsoft's error 91 page traces to a missing Set. Other languages creep in too: Dim total As Double = 0 or Try...Catch. VBA accepts neither.

5. It assumes Find or MATCH always succeeds

Per Microsoft's Range.Find reference, Find returns Nothing when there is no match, so .Find(x).Row fails when an invoice is missing. The same page says LookIn, LookAt, SearchOrder and MatchByte are saved between uses, including from the Find dialog. Unless LookAt is xlWhole, passed or inherited, Find matches part of a cell, so a search for INV-10 can land on INV-1001. Pass LookAt:=xlWhole and test for Nothing. WorksheetFunction.Match and VLookup raise a run-time error on no match, so guard them too.

6. It uses Integer for row numbers

VBA's Integer tops out at 32,767, per Microsoft's data type summary, but a worksheet has 1,048,576 rows. A long export then stops with an Overflow error. Use Long.

7. It writes 32-bit Windows API declarations

Microsoft's 64-bit VBA overview says 64-bit has been the default install since Office 2019 and Microsoft 365, every Declare needs PtrSafe, and pointers and handles need LongPtr. PtrSafe alone gets past the compiler but can still return truncated values.

8. It uses a pattern Microsoft is retiring

CreateObject("VBScript.RegExp") relies on VBScript, which Microsoft is deprecating. Its September 2025 guidance says Microsoft 365 Version 2508 (Build 19127.20154) and later include RegExp in VBA, late binding keeps working on those builds, and new macros should use the built-in RegExp. Scripting.Dictionary and FileSystemObject are unaffected. The risk is colleagues on older builds once VBScript is off by default, expected around 2026 or 2027; see the VBScript deprecation timeline and what replaces VBA.

9. It hides errors or leaves Excel in a bad state

On Error Resume Next left on for a whole procedure skips every later failure silently. Also check that ScreenUpdating, Calculation and EnableEvents are restored on every exit, including after an error.

10. Its UserForm buttons never fire

When a form's controls are added in code as it loads, a handler such as Private Sub btnSave_Click() is connected to nothing. Declare Private WithEvents btnSave As MSForms.CommandButton at the top of the form's module and Set it to the control when you add it. For many buttons, use a class module holding the WithEvents variable and handler, and keep its instances alive while the form is open.

A broken macro and the corrected version

Here is a first draft that looks finished, written by us to show several faults at once. The job: find an invoice number in column A of the Invoices sheet, then write "Paid" in column F and today's date in column G.

Sub MarkInvoicePaid()
    Dim ws As Worksheet
    Dim r As Integer
    Dim invNo As String

    invNo = InputBox("Invoice number?")
    ws = Sheets("Sheet1")
    ws.Select
    r = ws.Range("A:A").Find(invNo).Row
    Cells(r, 6).Value = "Paid"
    Cells(r, 7).Value = Date
End Sub

The faults surface one at a time:

We also ran this draft through the linter in HISAB 360's release build (1.0.13) on 16 September 2026. It reported three errors (the missing Set, no error handling, and the hard-coded Sheet1 lookup with no guard), two warnings (no Option Explicit, and Find without explicit LookIn and LookAt), plus suggestions and hints including that r should be a Long. When HISAB's AI writes a procedure, Error-level findings undo the write, so this draft would have gone back for a rewrite. The linter did not flag the missing not-found check, which is why reading the logic still matters. The corrected version below came back with no findings at all.

The corrected version. Change SHEET_NAME and the columns to suit, and keep the code in the workbook it updates (ThisWorkbook is the file containing the code):

Option Explicit

Public Sub MarkInvoicePaid()
    Const SHEET_NAME As String = "Invoices"   ' change to your sheet name
    Dim ws As Worksheet
    Dim found As Range
    Dim invNo As String
    Dim findText As String
    Dim rowNum As Long

    On Error GoTo Fail

    invNo = Trim$(InputBox("Invoice number to mark as paid:", "Mark invoice paid"))
    If Len(invNo) = 0 Then Exit Sub            ' Cancel or blank entry

    ' Treat ~ * ? as ordinary characters, not wildcards
    findText = Replace(Replace(Replace(invNo, "~", "~~"), "*", "~*"), "?", "~?")

    Set ws = ThisWorkbook.Worksheets(SHEET_NAME)
    Set found = ws.Columns("A").Find(What:=findText, _
        After:=ws.Cells(ws.Rows.Count, "A"), LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False)

    If found Is Nothing Then
        MsgBox "Invoice " & invNo & " is not in column A of " & SHEET_NAME & ".", _
               vbExclamation, "Mark invoice paid"
        Exit Sub
    End If

    rowNum = found.Row
    ws.Cells(rowNum, "F").Value = "Paid"
    ws.Cells(rowNum, "G").Value = Date
    Exit Sub

Fail:
    MsgBox "The invoice could not be updated: " & Err.Description, _
           vbExclamation, "Mark invoice paid"
End Sub

What changed: Set and ThisWorkbook on every object, the sheet name in one constant, a whole-cell search that treats *, ? and ~ literally (Excel's Find guidance treats them as wildcards), a not-found message, a Long row, a clean exit on Cancel, and an error message instead of the debugger.

It uses xlFormulas because column A holds typed invoice numbers, and a 2010 Microsoft support answer on the MSDN forums describes Find with xlValues ignoring hidden cells, which would miss filtered rows. If column A holds formulas, clear filters and use xlValues.

How to prompt ChatGPT for Excel VBA that works

Most of these fixes can be asked for up front: give the assistant the facts it would otherwise guess, and state your standards.

1. Describe the workbook: sheet names, header row, what each column holds, whether numbers are stored as text, roughly how many rows. Use made-up sample rows.
2. Say where it runs: Windows desktop Excel, version, 32-bit or 64-bit (File > Account > About Excel). Microsoft's page on VBA in Excel for the web confirms you can't create, run or edit VBA there.
3. State your rules: Option Explicit, no Select, qualified ranges, Long for rows, explicit LookIn and LookAt, a not-found message.
4. Ask for its assumptions first, then correct the wrong ones.
5. Build big jobs one procedure at a time, and report failures precisely: the error number, message and highlighted line.

For example:

Write an Excel VBA macro for Windows desktop Microsoft 365, 64-bit.
Workbook: sheet "AR Ledger", headers in row 4, data from row 5.
Column A = invoice number (text), E = amount, F = status.
Task: for every row where F is blank and E is zero or negative, write "Check" in F.
Rules: Option Explicit; no Select or Activate; qualify every range to
ThisWorkbook.Worksheets("AR Ledger"); Long for row numbers; last row
from column A; restore ScreenUpdating on exit, including after an error.
Before the code, list every assumption you are making.

Keep client names, bank details and payroll figures out of the chat, and follow your firm's AI policy.

Checklist: verify AI-written VBA before it touches real numbers

1. Work on a copy. Microsoft's Application.Undo reference says Undo cannot reverse Visual Basic commands.
2. Compile it: Debug > Compile VBAProject catches syntax and declaration errors, not wrong logic.
3. Read every destructive line (Delete, Clear, overwrites, SaveAs, Kill, email) and ask which sheet and rows it hits.
4. Search the module for Select, ActiveSheet, "Sheet1" and hard-coded addresses, and give every Find and lookup a not-found path.
5. Step through with F8 on a small test sheet.
6. Test awkward data: an empty sheet, one row, duplicates, blanks, numbers stored as text, filtered rows, and more than 32,767 rows.
7. Tie out control totals before and after.
8. Check how colleagues will open it. Office blocks macros by default in files from the internet, per Microsoft's guidance on macros blocked in internet files. See how to enable macros in Excel safely.

Still failing? Work through why an Excel macro is not working. For a free code review, Rubberduck runs inspections inside the VBA editor; it is GPLv3, and its repository was archived on 8 March 2026 (last stable release v2.5.91, November 2023). See our Rubberduck alternatives guide.

How HISAB 360 handles AI-written VBA

HISAB 360 is an AI assistant inside Windows desktop Excel. Unlike our free generator, it works on the workbook itself, and lints the procedures its AI writes or replaces before keeping them.

It writes into the real VBA project. Once you allow it, HISAB's AI can read existing macros, create standard and class modules and UserForms, write or replace procedures, add worksheet buttons wired to macros, and run macros. It can also add a custom ribbon tab and save as an .xlam add-in for you to install, install the VBA-Web and VBA-JSON helpers, start forms from five styled templates, and open existing forms in a visual designer.

A lint gate on writes. HISAB's static analyser for VBA has 142 inspections, covering several faults above: missing Set, unchecked Find or Find without explicit LookIn and LookAt, As Integer, reliance on the active sheet, On Error Resume Next left on, Application settings never restored, and Declare without PtrSafe. Code the AI writes with its write-macro or replace-macro tools is linted; an Error-level finding not suppressed with an @Ignore comment undoes the change and goes back to the AI to rewrite. Most checks listed here are warnings or hints, which don't block on their own, although some, such as an unchecked Find inside a public macro, are raised to errors and do block.

An optional sandbox test. The AI can apply riskier changes to a temporary copy of the workbook, run a compile check and smoke-test macros there. The compile check is a probe, not a guaranteed whole-project compile, and smoke tests run for real, so effects outside the workbook aren't sandboxed. The tested changes are never applied to the live workbook; to keep them, the AI makes them again there. The workbook must already be saved, and HISAB saves it first if it has unsaved changes. A clean probe can report "safe to apply" with no smoke tests run, so ask for them.

The limits, plainly:

• Static analysis, not the VBA compiler. It won't catch every compile error, mostly doesn't member-check late-bound Object or Variant code, and reports rather than auto-fixes. It is also strict: some rules block code that would compile, such as a longer public macro with no error handler or a function returning an array.
• Only the write and replace tools are gated. Imported modules, the VBA-Web and VBA-JSON helpers and form templates skip the gate, and suppression comments can let a flagged Error through.
• No automatic rollback. Keep a backup copy of the file; HISAB's module export saves code text only, not a hand-built form's layout.
• Off by default. Editing needs HISAB's Edit and Run macro permissions plus Excel's "Trust access to the VBA project object model", which Microsoft's security notes call a possible security hazard. Writing, running and testing macros need a .xlsm, .xlsb, .xlam or .xltm file.
• Other limits. You set VBA references yourself; UserForm controls are created in code when the form loads, not placed on the design surface; Windows desktop Excel only; a multi-module tool takes several guided steps, not one click.

Good for: finance teams maintaining macros in real workbooks who want AI-written procedure edits linted before they are kept. Not for: Mac or browser users, teams whose IT policy rules out trusting access to the VBA project, or anyone needing one snippet. See the tutorial on generating VBA macros and a ribbon from chat.

Frequently asked questions

Try HISAB 360 on your own workbook

HISAB 360 is an AI assistant inside Windows desktop Excel for accountants and finance teams. It can write VBA into a macro-enabled workbook once you switch on its macro permissions, and it connects two-way to QuickBooks, Xero, Zoho Books, Odoo and FreshBooks. The 15-day trial is the full product, no card required.