Checking ChatGPT's VBA Before It Touches the Ledger: the Faults That Never Throw an Error

Guide · 16 September 2026 · ~15 min read

A junior asks ChatGPT for a macro to mark invoices paid from a remittance advice. The code compiles. It runs. There is no error dialog, and the file still foots. Two weeks later a client rings about a statement chasing an invoice they settled in March, and INV-1001, which nobody has paid, is stamped Paid in the ledger. The macro did exactly what it was written to do: Range("A:A").Find("INV-10") returned the first cell containing that text, and that cell was INV-1001.

That is the shape of the AI-VBA problem in an accounting firm. The macros that crash are the safe ones. You see run-time error 91, you fix it, nothing has moved. The dangerous ones run to the end, touch a few hundred rows, and hand back a ledger that looks finished. Excel will not help you undo it either: Microsoft's Application.Undo reference says Undo cannot reverse Visual Basic commands, so a macro that overwrites a closed period has overwritten it.

This page is the review, not the tutorial. The ten faults below are sorted by whether you would notice them or not; the worked example is an accounts-receivable macro in both its broken and corrected form, with what each fault does to the ledger rather than which error number it raises; and the checklist at the end is the one to run before an AI-written macro is allowed near a client file.

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

The short version: the macro that runs clean is the one to worry about

• AI writes VBA that compiles and runs. ChatGPT, Claude and Gemini all produce VBA as text you paste into a module, and most of it is syntactically sound.
• The faults that cost you money do not raise an error. They write correct-looking values into the wrong rows, or onto the wrong sheet, and finish without a dialog.
• Nothing an AI wrote goes near a live ledger until it has run on a copy and the control totals tie.
• Whichever AI tool you use, compile the code, read it and test it on a copy before it touches real numbers.

Getting the code into a workbook — on a copy, never the live file

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.

1. Save a copy of the workbook holding only the period you are testing, and close the live file, so that a macro addressing workbooks by name cannot find it.
2. Save that copy as a macro-enabled .xlsm file.
3. Press Alt+F11, then Insert > Module, and paste the code.
4. Run Debug > Compile VBAProject and fix anything it flags.
5. Press Alt+F8 and run the macro on the copy.
6. 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.

What the Excel-resident assistants will and won't touch

Assistants that run inside Excel can see the workbook, but not all of them will write VBA. Anthropic's Claude for Excel documentation says it answers questions about open workbooks, but lists "Macros and VBA operations" as unsupported. Microsoft's Get started with Copilot in Excel page, checked on 16 September 2026, lists editing cells and sheets, formulas, charts, PivotTables, formatting, sorting, filtering, web search, importing data and skills, and does not mention VBA or macros either way; Copilot changes often, so try it yourself. 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. For what Copilot does and does not reach in a close, see Copilot in Excel's limits for accountants.

Who checks the code before it runs?

There is no independent VBA benchmark, and for a reviewer the interesting question is not which model writes the neatest code. It is whether anything inspects the code before it edits a ledger, and whether the thing writing it can see the workbook it is about to change while it changes it.

No entry in that table signs the work off. Whichever you use, the reviewer's checklist below is still a person's job. For a wider comparison, see AI Excel tools for accountants.

Ten faults, sorted by whether you would notice

None of these faults is exotic; each slips through because the code reads naturally. What matters to a reviewer is the order. Four of them leave no trace at all, so read for those first.

Faults that change your numbers and say nothing. The macro finishes, no dialog appears, and the ledger is wrong.

The partial match that pays the wrong invoice

This is the fault from the top of the page, and it is the one that costs money. Per Microsoft's Range.Find reference, Find returns Nothing when there is no match, so .Find(x).Row fails when an invoice is missing. That failure is the harmless one: you get error 91 and nothing has moved. The expensive one is that unless LookAt is xlWhole, passed or inherited, Find matches part of a cell, so a search for INV-10 lands on the first cell containing those characters — on a ledger sorted by invoice number, INV-1001. Paid and today's date go onto that row, the count of rows marked paid is exactly right, and nothing on screen looks wrong.

The same page says LookIn, LookAt, SearchOrder and MatchByte are saved between uses, including from the Find dialog. A macro that does not pass them inherits whatever was last typed into Ctrl+F on that PC, so the same code can behave one way on your machine and another way on a colleague's, and neither of you sees an error. Pass LookAt:=xlWhole explicitly and test for Nothing. WorksheetFunction.Match and VLookup raise a run-time error on no match, so guard them too.

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. The guess is most dangerous when it resolves to something real: in a workbook carrying a tab per period, a macro aimed at Sheet1 can write into a month you have already reported, while the month you meant to update stays untouched and looks it.

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"). The ledger consequence is that the row numbers are right and the sheet is wrong, so the damage lands on whichever tab somebody left in front — and row 412 of the wrong sheet is a real row with real figures on it.

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. For ledger work this is the worst of the four, because a run that half-failed and a run that worked look identical: a macro that updated 180 rows of 400 reports nothing at all. Calculation left on manual is its own quiet misstatement, since every total on the sheet then shows the answer from before the run.

Faults that stop the macro. These six announce themselves with a compile error, a dialog or a highlighted line, so they cost you an afternoon rather than a client. Fix them, but do not mistake fixing them for a review.

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.

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.

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. A general ledger for a busy year clears 32,767 lines easily, so this one is a question of when, not whether.

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.

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 deadline and how to sweep your workbooks for it.

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 AR macro, what it did to the ledger, 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

Read it as a reviewer would, by what each line does to the ledger rather than by which dialog it raises:

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.

Control totals: proving the macro changed only what it should

Reading the code tells you what it was meant to do. Control totals tell you what it did. Before the run, on a scratch sheet in the test copy, record four numbers:

1. The row count of the ledger sheet.
2. The SUM of the amount column.
3. The count of blank cells in the status column.
4. The receivables control account balance the ledger is meant to agree to.

Run the macro, take the same four again, and put them side by side. The row count and the amount total must not move at all: a macro that marks invoices paid has no business adding, deleting or re-valuing a row, so any movement there ends the test. The count of blanks in the status column should fall by exactly the number of invoices on the remittance — no more, and no fewer. Whether the control balance moves depends on what your process expects; decide that before you look, not after.

Then the uncomfortable part. Run the broken version above against a remittance of twelve invoices and it passes three of those four checks. The row count is unchanged. The amount total is unchanged. Twelve status cells were filled, because twelve rows were stamped. They were simply the wrong twelve. Counting cannot catch a substitution, and every check that counts things will sign this macro off.

So add one that does not count. Tie the rows the macro touched back to the remittance line by line: invoice number and amount from the advice against invoice number and amount on each row now marked Paid. Filtering the status column on Paid and the date column on today gives you that list in a few seconds. It is the only check here that would have found INV-1001, and on a first live run it is worth doing for every row rather than a sample.

The workbook facts the chat window cannot see (and the client data you must not paste)

Most of these faults can be pre-empted: 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, written around a real receivables ledger rather than a tidy one:

Write an Excel VBA macro for Windows desktop Microsoft 365, 64-bit.
Workbook: sheet "AR Ledger", headers in row 4, data from row 5,
about 9,000 rows.
Column A = invoice number, stored as text, some with leading zeros.
Column C = document date. Column E = amount; credit notes are negative.
Column F = status.
Task: for every row dated on or after 01/04/2026 where F is blank and
E is zero or negative, write "Check" in F. Rows dated before 01/04/2026
are a closed period and must not be written to at all.
Rules: Option Explicit; no Select or Activate; qualify every range to
ThisWorkbook.Worksheets("AR Ledger"); Long for row numbers; last row
from column A; compare invoice numbers as text, never as numbers;
restore ScreenUpdating on exit, including after an error.
Before the code, list every assumption you are making.

The closed-period line is the one to keep in every prompt of this kind. An assistant given no cut-off will happily write over a period you have already reported, and the resulting change is invisible in the file.

What not to paste into a public chat window

Client names, bank details, account numbers, payroll figures and anything else covered by the engagement's confidentiality terms stay out of a public chat window. None of it is needed to get working code. Describe the shape of the data instead — column letters, data types, roughly how many rows — and invent three or four sample rows to show the awkward cases. A macro written against "Customer A" and a macro written against a real client name are the same macro. Where exactly the line sits is your firm's AI policy to draw, not ours; ask before the first prompt rather than after it.

The reviewer's checklist: sign this off before it runs on a client file

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. Have somebody else read it. Not the person who asked for the macro: they know what it is supposed to do, which is exactly what stops them seeing what it does. A second reader takes the destructive lines only, and that is a ten-minute job.
6. Step through with F8 on a small test sheet.
7. Test awkward data: an empty sheet, one row, duplicates, blanks, numbers stored as text, filtered rows, and more than 32,767 rows.
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.
9. Restrict the first live run. One period, or one customer, or the ten rows of a single remittance. Check that result by hand before the macro is let at the rest of the file.
10. Write down what was run. Which macro, which version of it, on which file, by whom and when. A macro leaves no audit trail of its own, and that record is what you will be asked for when a figure is queried six months later.
11. Tie out control totals before and after, and tie the touched rows back to the source document line by line. This is the last gate, and nothing goes to a client file until it is clean.

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. The lint gate and the sandbox test below are this product's answer to the "checks before it runs" column above; neither of them replaces the control totals, and neither of them reads your remittance advice.

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.