Excel/VBA Agentic Coding Guide

EXCEL_VBA_AGENTIC_CODING.md

Excel/VBA Agentic Coding Guide (from a real build/debug session)

Purpose

This document captures practical lessons from building a non-trivial .xlsm with generated VBA modules, Excel COM automation, and iterative debugging.
It is meant for:

• Expert Excel/VBA developers starting with agentic coding workflows
• Prompt engineers directing coding agents on Excel/VBA tasks

It focuses especially on how to handle compilation errors fast.

The "Zellij" project mentioned throughout was a small example used to explore the workflow.

---

What was built in this session

The session produced an Excel macro workbook (ZellijImageGenerator.xlsm) that:

• exposes generator options on a front sheet (Generator)
• has buttons to generate output / create new phrase
• renders generated images into a worksheet grid as cell colorings (Image)

Implementation used:

• VBA modules generated/imported from .bas files
• PowerShell COM automation to create workbook and wire buttons/macros
• headless macro execution for testing

---

Core workflow that worked

1. Keep VBA source in plain-text .bas files under version control.
2. Use a script (CreateZellijXlsm.ps1) to:
   • create workbook
   • create/configure sheets
   • import all modules
   • save as .xlsm
3. Validate by running macros through COM in a scripted loop.
4. If compile/runtime UI blocks COM, use UI automation to read dialogs and selected code line.
5. Patch .bas files, rebuild .xlsm, rerun tests.

This is significantly more reliable than manual edits directly in VBE.

---

Key technical lessons about VBA coding

1) Treat VBA as strict, old parser infrastructure

VBA parser behavior can be brittle in ways modern languages are not.

Practical rules:

• Keep procedure signatures and long calls short and readable.
• Use line continuation (_) aggressively for long declarations/calls.
• Avoid “too clever” one-liners in declarations.
• Keep naming consistent and explicit.

In this session, some procedures existed in text but were not recognized by compilation/introspection until signatures were restructured and shortened.

2) Prefer module-level structure with narrow responsibilities

Good split used here:

• modZellijCore: RNG, seed hashing, palette types
• modZellijRaster: low-level rasterization primitives
• modZellijGenerator: pattern generation logic
• modZellijExcel: sheet IO, rendering, button entry points

This makes compile/debug localization much faster.

3) Array passing is a common source of compile trouble

Fixed-size vs dynamic array declarations can cause surprises in ByRef calls between procedures.

Practical rule:

• Standardize on dynamic arrays (Dim a() As Double, ReDim ...) for array parameters passed between procedures.

This reduced ambiguity and removed compile-path instability.

4) Excel-grid rendering performance requires batching

Coloring cell-by-cell is very slow.

Used optimizations:

• pre-clear background in one range operation
• set row/column sizing in bulk
• color runs of same color per row (run-length style range writes)

For large outputs, this is the difference between usable and unusable.

5) Numeric overflow is easy to hit in VBA

Long is signed 32-bit. Intermediate conversions (CLng) overflow quickly.

Session example:

• runtime overflow in SHA/byte writing path (WriteBe32)
• fixed by avoiding unsafe CLng conversions on large unsigned-like values
• extracted bytes via staged subtraction/division to stay in safe ranges

Rule: when emulating unsigned 32-bit arithmetic, assume overflow risk everywhere.

---

Compilation errors: a concrete debugging playbook

This is the most important part for agentic workflows.

Problem pattern

When Excel.Application.Run("MacroName") hits compile errors:

• Excel/VBE opens compile dialog
• VBE highlights a token/line
• COM automation call often hangs or fails with RPC errors (0x800706BE)

If you cannot read the dialog and highlighted line, iteration is slow.

Solution pattern that worked

Use UI Automation to read:

• compile dialog text (e.g., “Sub or Function not defined”)
• selected token/line from code pane document (TextPattern.GetSelection())

Then patch based on exact failing symbol.

UI Automation call pattern used in practice

The session used System.Windows.Automation from PowerShell (pwsh) with this sequence:

1. Load UIA assemblies.
2. Find top-level windows (TreeScope.Children) and pick target window by title:
   • VBE compile state window: Microsoft Visual Basic for Applications - [<Module> (Code)]
   • Runtime error message window (Excel): workbook title, e.g. Zellij
3. Extract dialog text:
   • Search descendants for ControlType.Text
   • Read .Current.Name from each text element
4. Extract highlighted failing code:
   • Find ControlType.Document in VBE code pane
   • Get TextPattern
   • Read GetSelection() to capture highlighted token
   • Expand selection to line with ExpandToEnclosingUnit(3) (line unit) to capture full call line
5. Dismiss blocking dialogs:
   • Find descendant button with name OK
   • Invoke via InvokePattern
   • Resume COM macro loop

Minimal pattern:

Add-Type -AssemblyName UIAutomationClient, UIAutomationTypes
$root = [System.Windows.Automation.AutomationElement]::RootElement

# 1) Find VBE window
$wins = $root.FindAll([System.Windows.Automation.TreeScope]::Children, [System.Windows.Automation.Condition]::TrueCondition)
$vbe = $null
for ($i=0; $i -lt $wins.Count; $i++) {
  $w = $wins.Item($i)
  if ($w.Current.Name -like 'Microsoft Visual Basic for Applications*') { $vbe = $w; break }
}

# 2) Compile dialog text
$textCond = New-Object System.Windows.Automation.PropertyCondition(
  [System.Windows.Automation.AutomationElement]::ControlTypeProperty,
  [System.Windows.Automation.ControlType]::Text
)
$texts = $vbe.FindAll([System.Windows.Automation.TreeScope]::Descendants, $textCond)
for ($j=0; $j -lt $texts.Count; $j++) {
  $name = $texts.Item($j).Current.Name
  if ($name) { $name }
}

# 3) Highlighted code token/line
$docCond = New-Object System.Windows.Automation.PropertyCondition(
  [System.Windows.Automation.AutomationElement]::ControlTypeProperty,
  [System.Windows.Automation.ControlType]::Document
)
$docs = $vbe.FindAll([System.Windows.Automation.TreeScope]::Descendants, $docCond)
$doc = $docs.Item(0)
$tp = $doc.GetCurrentPattern([System.Windows.Automation.TextPattern]::Pattern)
$sel = $tp.GetSelection()
$line = $sel[0].Clone()
$line.ExpandToEnclosingUnit(3) | Out-Null
$line.GetText(2000)

Practical notes:

• In PowerShell, $PID is reserved; do not use $pid for your own process-id variable.
• Expect both titled and untitled Excel processes/windows during COM runs.
• If COM call fails with RPC after compile popup, read/click UI first, then continue automation.
• UIA selectors should prefer control type + short title pattern; exact titles vary by locale/version.

Useful signals captured in this session

• Compile dialog text
• Highlighted token (DrawEightPointStarPattern)
• Highlighted call line in modZellijGenerator
• Runtime error message shown in Excel window (Generate failed: Overflow ...)

Additional introspection that helped

Using VBIDE CodeModule via COM:

• ProcStartLine
• ProcOfLine
• Find
• dumping module text from workbook

If ProcStartLine("SomeProc") fails while text contains it, parser likely did not register the procedure properly (often syntax/line-shape issue nearby).

Fast compile-error loop

1. Run macro headless via COM.
2. If hang/RPC: inspect UI (dialog + selected line).
3. Capture exact missing symbol and call site.
4. Validate called procedure declaration text in imported workbook module.
5. Simplify declaration/call shape (split lines, shorten names, reduce complexity).
6. Rebuild workbook from source modules.
7. Repeat.

---

Runtime errors: practical handling

When compile is clean but execution fails:

• Add lightweight stage markers (e.g., global gZellijStage)
• Update marker at critical checkpoints
• Include marker in error message surface (MsgBox ... at <stage>)

This gave a precise location for overflow in hashing byte-output.

Keep this instrumentation temporary or behind a debug flag.

---

COM automation lessons

1) Enable VB project import access

Programmatic module import requires Trust Center VBOM access.

Session used registry key:

• HKCU\Software\Microsoft\Office\16.0\Excel\Security\AccessVBOM = 1

2) Macro security mode matters

When opening workbook for execution:

• enable macros for runtime test (AutomationSecurity = 1 in this session)

When opening for static inspection:

• disable macros (AutomationSecurity = 3) to avoid side effects

3) COM cleanup must be explicit

Always close workbook and quit Excel in finally and release COM objects.
If a dialog is open, COM may still break; UI-close dialog first, then cleanup.

---

Prompting guide for agentic Excel/VBA sessions

Use prompts that force an evidence-driven loop.

Prompt template (recommended)

“Build <feature> in .xlsm via source-controlled .bas modules and a reproducible build script.
After each build, run headless macro tests.
If compile/runtime error occurs, capture exact UI dialog text and highlighted code line before patching.
Do not guess. Report the root cause and fix.”

Prompt constraints that help

• “Keep VBA procedure signatures short and multiline.”
• “Prefer dynamic arrays for ByRef array parameters.”
• “Instrument runtime errors with stage markers.”
• “Use direct pwsh commands; avoid nested shell indirection.”
• “After fix, rerun macro and provide proof of success.”

Ask the agent to return these artifacts

• .bas module files
• workbook build script
• test runner script/snippets
• known limitations section

---

Things that were uncertain / likely process improvements

1) Exact root cause of “declared but unresolved procedure”

Observed behavior strongly suggested parser registration issues from line/procedure shape, but exact VBA internal rule was not fully proven.

Improvement:

• add automated line-length and declaration-shape lint checks before import.

2) UI Automation robustness across environments

UIAutomation worked here, but may vary by:

• Office version
• UI language/localization
• window focus/security contexts

Improvement:

• create a reusable UIA helper script with fallback selectors and localized message patterns.

3) Pixel mapping in Excel grid

RowHeight/ColumnWidth to pixel equivalence is approximate and font/display dependent.

Improvement:

• add a calibration routine (measure target range width/height and tune).

4) Compile step API

A direct “compile project now and return diagnostics” API is limited in native COM workflow.

Improvement:

• investigate robust compile command invocation through VBE command bars or external VBA tooling integration.

---

Recommended standard for future VBA agentic projects

1. Source-first: all VBA in text modules under git.
2. Reproducible build: one script generates .xlsm.
3. Automated smoke test: run entry macro headlessly.
4. UI capture on failure: always collect compile/runtime dialog + highlighted line.
5. Evidence-based patching: no blind guesses.
6. Short declarations: avoid parser edge cases.
7. Numeric safety review: especially unsigned-like arithmetic.
8. Close-loop proof: rerun test and report success evidence.

That process reduced debugging time substantially and turned opaque VBA failures into a manageable engineering loop.