Office Macros skill for Codex

Done well means: the macro runs in seconds on real data sizes, leaves Excel in the state it found it (screen updating, calculation, events) even after an error, and the user can read, rerun and trust it. Choose the platform first; most macro pain comes from picking the wrong one.

Choose VBA or Office Scripts

Situation	Choose
Desktop Excel/Word/Outlook only, needs file system, other apps, UserForms	VBA
Workbook lives in OneDrive/SharePoint, used in Excel on the web	Office Scripts
Must run unattended on a schedule or from a form/email trigger	Office Scripts + Power Automate
Shared with Mac users	Office Scripts (VBA on Mac lacks Windows APIs and ActiveX)
Pure data transformation that could live outside Excel	Python: $spreadsheet-automation
A formula could do it	Write the formula: $excel-formulas

Workflow

- [ ] 1. Get a copy of the workbook and a description of one manual run, step by step
- [ ] 2. Inspect structure: sheet names, tables (ListObjects), named ranges, data sizes
- [ ] 3. Write the macro against a copy, never the user's only file
- [ ] 4. Run on the copy; compare output with one manual run
- [ ] 5. Add the error handler, logging and a confirmation before destructive steps
- [ ] 6. Deliver as .bas/.ts source plus instructions, not only inside a binary file

Codex cannot open the VBA editor. Work with exported modules (.bas, .cls, .frm) as text. To read macros already inside an .xlsm or .docm, olevba workbook.xlsm from the oletools package prints them (pip install needs network). An .xlsm is a zip whose code sits in a binary vbaProject.bin; never edit that blob directly.

VBA patterns

Skeleton with state restore

Option Explicit

Public Sub BuildMonthlySummary()
    Dim calcMode As XlCalculation
    calcMode = Application.Calculation
    On Error GoTo Fail

    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Data")
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    If lastRow < 2 Then Err.Raise vbObjectError + 1, , "No data rows on sheet Data"

    Dim src As Variant
    src = ws.Range("A2:D" & lastRow).Value2          ' one read: 2-D array, 1-based

    Dim totals As Object
    Set totals = CreateObject("Scripting.Dictionary")
    Dim r As Long
    For r = 1 To UBound(src, 1)
        totals(src(r, 2)) = totals(src(r, 2)) + src(r, 4)
    Next r

    Dim out() As Variant, k As Variant, i As Long
    ReDim out(1 To totals.Count, 1 To 2)
    For Each k In totals.Keys
        i = i + 1
        out(i, 1) = k: out(i, 2) = totals(k)
    Next k

    With ThisWorkbook.Worksheets("Summary")
        .Range("A2:B" & .Rows.Count).ClearContents
        .Range("A2").Resize(UBound(out, 1), 2).Value2 = out   ' one write
    End With

Cleanup:
    Application.Calculation = calcMode
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Exit Sub
Fail:
    MsgBox "BuildMonthlySummary failed: " & Err.Description, vbExclamation
    Resume Cleanup
End Sub

Rules that make VBA fast and predictable:

• Option Explicit in every module; declare Long, not Integer (overflows at 32,767).
• Never Select or Activate; reference ranges through a worksheet variable.
• Read and write whole ranges as arrays. A cell-by-cell loop over 50k rows takes minutes; the array version takes well under a second.
• Fully qualify: ThisWorkbook.Worksheets("Data"), not ActiveSheet or Sheets("Data") (the latter picks whichever workbook is active).
• Prefer ListObjects (ws.ListObjects("tblSales").DataBodyRange) over hard-coded ranges.
• Value2 skips Currency/Date coercion and is faster; format dates on output.
• Find the last row with End(xlUp) on a column that is always filled, not UsedRange.

Word and Outlook

• Word: edit through ActiveDocument.Content.Find with .Execute Replace:=wdReplaceAll and ranges; avoid moving the Selection.
• Outlook: create mail with Application.CreateItem(olMailItem), call .Display so the user reviews it. Use .Send only after the user explicitly confirms bulk sending, and expect the Outlook object-model guard prompt on unmanaged machines.

Office Scripts (Excel on the web)

function main(workbook: ExcelScript.Workbook, minAmount: number = 0): string {
  const data = workbook.getWorksheet("Data");
  if (!data) throw new Error("Sheet 'Data' not found");
  const used = data.getUsedRange();
  if (!used) return "Sheet is empty";

  const rows = used.getValues();                 // one read
  const header = rows.shift() as string[];
  const amountCol = header.indexOf("Amount");
  if (amountCol < 0) throw new Error("No 'Amount' column");

  const kept = rows.filter(r => Number(r[amountCol]) >= minAmount);
  let out = workbook.getWorksheet("Filtered");
  if (out) out.delete();
  out = workbook.addWorksheet("Filtered");
  out.getRangeByIndexes(0, 0, kept.length + 1, header.length)
     .setValues([header, ...kept]);              // one write
  return `${kept.length} rows kept`;
}

• main parameters after workbook become inputs in Power Automate; the return value becomes the action’s output.
• Keep get*/set* calls out of loops: each one is a round trip to the workbook. Read once, transform plain arrays, write once.
• Explicit any is not allowed; type values as (string | number | boolean)[][].
• getWorksheet, getTable and getUsedRange return undefined when missing; check.
• Scripts cannot reach the file system or other workbooks directly; Power Automate passes data in and out. External fetch works only when the script runs from Excel, not from Power Automate.
• Power Automate runs have a synchronous timeout and a daily call cap per user; check Microsoft’s current “Office Scripts platform limits” page before designing a large job, and split work into batches of rows.

Delivery and macro security

• Since 2022, Office blocks macros in files carrying the Mark of the Web (downloaded or emailed). The user unblocks via file Properties > Unblock, or IT adds a Trusted Location. Never advise lowering macro security to “Enable all macros”.
• Ship the source: .bas exports in the repo, plus a README with import steps (Alt+F11 > File > Import File) and the button or shortcut to bind.
• For reusable VBA across workbooks, suggest an .xlam add-in instead of copying modules.
• Sign macros when the organisation requires it; self-signed certificates only satisfy the machine that created them.

Gotchas

• On Error Resume Next left on hides every later failure. Scope it to one line and turn it off with On Error GoTo 0 or the handler label.
• Deleting rows in a forward loop skips rows. Loop backwards, or build a union range or filter and delete once.
• Regional settings: Range.Formula takes English function names and commas; FormulaLocal takes the user’s locale. Write Formula2 for dynamic arrays.
• Workbook_Open and worksheet events fire during your macro unless EnableEvents is off.
• Office Scripts recorded by the Action Recorder use hard-coded addresses; rewrite against tables or used ranges before reusing them.

Verification

• Run against a copy with production-sized data and time it (Timer in VBA).
• Compare output with a manual run on the same input: row counts and one checksum column.
• Force an error (rename the source sheet) and confirm the handler restores calculation, events and screen updating.
• For Office Scripts, run once from Excel, then once from the Power Automate test run, and read the returned string.

Handover

**Macro:** BuildMonthlySummary (Module modSummary.bas), Excel desktop, Windows and Mac
**Input:** sheet Data, columns A:D, header row 1
**Output:** sheet Summary A:B, cleared and rewritten each run
**Run:** button on Summary sheet, or Alt+F8 > BuildMonthlySummary
**Tested:** 48,210 rows in 0.6 s; totals match the manual pivot for September