Using AI to Write Excel VBA Macros Safely

Start With a Specification, Not a Wish

Platform: Excel 365 on Windows.
Workbook: "Weekly Sales.xlsm".
Input: sheet "Export", headers in row 1, data from row 2.
  A = Branch, B = Invoice No, C = Date, D = Customer, E = Amount.
Task:
  1. Delete rows where Amount is blank or zero.
  2. Sort by Branch, then Date (oldest first).
  3. For each Branch, copy its rows to a new workbook and save it as
     "Sales - <Branch> - <yyyy-mm-dd>.xlsx" in the same folder.
Rules:
  - Use Option Explicit and declare every variable.
  - Do not modify the original file; work on a copy of "Export".
  - If sheet "Export" is missing, show a message and stop.
  - Ask for confirmation before saving files.
  - Comment each block in plain English.

What Good AI-Written VBA Looks Like

Option Explicit

Sub SplitSalesByBranch()
    Dim ws As Worksheet, wsWork As Worksheet
    Dim lastRow As Long, r As Long
    Dim branches As Object, b As Variant
    Dim newWb As Workbook, outPath As String

    On Error GoTo Fail

    ' 1. Make sure the input sheet exists
    On Error Resume Next
    Set ws = ThisWorkbook.Worksheets("Export")
    On Error GoTo Fail
    If ws Is Nothing Then
        MsgBox "Sheet 'Export' not found.", vbExclamation
        Exit Sub
    End If

    If MsgBox("Create one file per branch in " & ThisWorkbook.Path & "?", _
              vbYesNo + vbQuestion) <> vbYes Then Exit Sub

    Application.ScreenUpdating = False

    ' 2. Work on a copy so the original export is untouched
    ws.Copy After:=ws
    Set wsWork = ActiveSheet
    wsWork.Name = "Work_" & Format(Now, "hhmmss")

    ' 3. Remove blank / zero amounts (loop bottom-up when deleting)
    lastRow = wsWork.Cells(wsWork.Rows.Count, "A").End(xlUp).Row
    For r = lastRow To 2 Step -1
        If Val(wsWork.Cells(r, "E").Value) = 0 Then wsWork.Rows(r).Delete
    Next r

    ' 4. Collect distinct branches
    Set branches = CreateObject("Scripting.Dictionary")
    lastRow = wsWork.Cells(wsWork.Rows.Count, "A").End(xlUp).Row
    For r = 2 To lastRow
        branches(CStr(wsWork.Cells(r, "A").Value)) = True
    Next r

    ' 5. Filter and export each branch
    For Each b In branches.Keys
        wsWork.Range("A1").CurrentRegion.AutoFilter Field:=1, Criteria1:=b
        Set newWb = Workbooks.Add(xlWBATWorksheet)
        wsWork.Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible) _
              .Copy newWb.Worksheets(1).Range("A1")
        outPath = ThisWorkbook.Path & Application.PathSeparator & _
                  "Sales - " & b & " - " & Format(Date, "yyyy-mm-dd") & ".xlsx"
        newWb.SaveAs outPath, FileFormat:=xlOpenXMLWorkbook
        newWb.Close SaveChanges:=False
    Next b

    wsWork.AutoFilterMode = False
    Application.DisplayAlerts = False
    wsWork.Delete
    Application.DisplayAlerts = True

Done:
    Application.ScreenUpdating = True
    MsgBox branches.Count & " branch files created.", vbInformation
    Exit Sub
Fail:
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    MsgBox "Stopped: " & Err.Description, vbCritical
End Sub

The 10-Minute Review Checklist

• Search for destructive keywords

  Delete, Kill, ClearContents, Clear, SaveAs, Close SaveChanges:=True, RmDir. Confirm each one acts on what you expect.

• Check every hard-coded name

  Sheet names, file paths and column letters must match your file exactly, including spaces.

• Check loops have an end

  Do While loops without a clear exit condition can freeze Excel. For-Next loops over Rows.Count can run a million times.

• Look for error handling

  On Error Resume Next with nothing after it hides every failure. It should be switched back with On Error GoTo.

• Check what it opens or sends

  Any code that sends email, calls a URL, or writes outside the workbook's folder deserves a second look.

• Run on a copy, step through with F8

  Press F8 in the VBA editor to run one line at a time and watch what happens.

Errors AI-Written VBA Hits Most Often

Prompts for Reviewing Code You Already Have

• Explain what this macro does step by step, and list anything it deletes, overwrites or sends.

• Add comments to this code without changing its behaviour.

• Add error handling so that ScreenUpdating and DisplayAlerts are always restored.

• This macro is slow on 50,000 rows. Rewrite it to use arrays instead of reading cells one at a time.

• Make this macro work on both Windows and Mac Excel, or tell me which parts cannot.

AI Prompting vs a VBA Developer