Preventing AI‑Generated VBA Failures in Reactor Diagrams

by

Summary

A production automation script designed to generate reactor engineering diagrams via Excel VBA failed to execute due to incomplete code generation and unhandled runtime exceptions. The user attempted to implement a complex geometric drawing routine provided by an AI, but the script terminated prematurely because the logic was truncated, leaving the execution environment in an inconsistent state.

Root Cause

The failure stems from three distinct layers:

  • LLM Truncation (Primary): The AI-generated code provided by the user was incomplete. The logic cuts off mid-calculation (hTop = Calc), resulting in a syntax error that prevents the VBA compiler from even parsing the module.
  • Lack of Input Validation: The script assumes specific sheet names ("Reactor Calculator" and "Reactor Diagram") and specific cell ranges exist. If these are missing or named differently, the Set commands trigger a Runtime Error 9 (Subscript out of range).
  • Type Mismatch Vulnerability: The use of CDbl and CLng on worksheet ranges without checking if the cells contain numeric data leads to Runtime Error 13 (Type Mismatch) if a user enters text or a blank space where a dimension is expected.

Why This Happens in Real Systems

In professional environments, this pattern manifests as “The Illusion of Completeness.”

  • Generative Tooling Risks: Engineers often use AI to scaffold boilerplate code. If the engineer does not verify the structural integrity of the output, they inherit “hallucinated” or truncated logic.
  • Brittle Integration: Code that relies heavily on hardcoded workbook structures (specific sheet names and cell addresses) is highly sensitive to environmental changes. A simple user rename of a tab breaks the entire automation pipeline.
  • Silent Failures in Batch Processing: In larger systems, if an error handler is poorly implemented (like On Error GoTo ErrHandler without a defined ErrHandler: label), the program will crash ungracefully, potentially leaving files locked or memory unallocated.

Real-World Impact

  • Operational Downtime: Engineers relying on automated diagrams for reactor design lose time troubleshooting “broken” tools rather than performing actual engineering tasks.
  • Data Integrity Risks: If a script fails halfway through a drawing routine, it can leave orphaned shapes or partially drawn diagrams, leading to incorrect visual interpretations of critical vessel dimensions.
  • Technical Debt: Using unverified, AI-generated “black box” code increases the maintenance burden on the team, as the original logic intent is lost once the code is broken.

Example or Code (if necessary and relevant)

' PROPER IMPLEMENTATION PATTERN
Sub SafeDrawReactor()
    Dim wsCalc As Worksheet
    Dim Di As Double

    On Error GoTo ErrHandler

    ' 1. Validate Workbook Structure
    On Error Resume Next
    Set wsCalc = ThisWorkbook.Sheets("Reactor Calculator")
    On Error GoTo ErrHandler

    If wsCalc Is Nothing Then
        MsgBox "Error: 'Reactor Calculator' sheet not found!", vbCritical
        Exit Sub
    End If

    ' 2. Validate Data Types before casting
    If Not IsNumeric(wsCalc.Range("C5").Value) Then
        MsgBox "Error: Diameter (C5) must be a number.", vbExclamation
        Exit Sub
    End If

    Di = CDbl(wsCalc.Range("C5").Value)

    ' ... rest of logic ...
    Exit Sub

ErrHandler:
    MsgBox "An unexpected error occurred: " & Err.Description, vbCritical
End Sub

How Senior Engineers Fix It

  • Defensive Programming: Instead of assuming data is correct, we implement Guard Clauses. We check If IsNumeric(...) and If Not ws Is Nothing before attempting to use the variables.
  • Decoupling Configuration from Logic: Instead of hardcoding "C5", we use Named Ranges in Excel. This allows users to move cells around without breaking the VBA code.
  • Structured Error Handling: We never use On Error GoTo without a corresponding ErrHandler: label and a strategy to either recover, log, or exit gracefully.
  • Unit Testing Logic: We break large routines (like drawing a whole reactor) into smaller, testable functions (e.g., GetVesselDimensions, DrawCylinder, DrawAgitator).

Why Juniors Miss It

  • The “Happy Path” Bias: Juniors tend to write code for when everything works perfectly, ignoring the “edge cases” where users enter text, delete rows, or rename sheets.
  • Over-Reliance on Tooling: There is a tendency to treat AI output as verified truth rather than a draft. Juniors often skip the manual step of compiling and dry-running the code.
  • Lack of Contextual Awareness: Juniors often focus on the syntax (making the code “run”) rather than the robustness (making the code “survive” a real-world user).

Leave a Comment