Govert van Drimmelen govert

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.

Overview

The issue concerns the behaviour of Excel's RTD feature when:

the RTD call is made from a wrapper function,
the wrapper function is called from an array formula, and
in one of the calculation calls, the wrapper function does not make an RTD call.

The expected behaviour is that the RTD server gets a DisconnectData call for the relevant RTD topic.

The observed behaviour is that the RTD server does not get a DisconnectData call.

Contributor License Agreement (CLA)

This CLA specifies how the Excel-DNA project is allowed to use your code.

When you make a contribution to the Excel-DNA project, you agree:

The code you wrote is your original work (you own the copyright) or you otherwise have the right to submit the work.
To grant the Excel-DNA project and its coordinator(s) a nonexclusive, irrevocable license to use your submitted code in any way.
You are capable of granting these rights for the contribution.

	using System.Threading.Tasks;
	using System.IO.MemoryMappedFiles;
	using System.Runtime.InteropServices;
	using System.Security.Cryptography;
	using System.Runtime.Serialization;
	using System.Runtime.Serialization.Formatters.Binary;

	async Task Main()
	{
	using (var channel = DemoServer())

	using System;
	using System.Diagnostics;
	using ExcelDna.Integration;
	using static ExcelDna.Integration.XlCall;

	namespace SetValuePerf
	{
	public static class Macros
	{

	Option Explicit On
	Imports ExcelDna.Integration ' for ExcelDnaUtil, XlCall, etc
	Imports System.Math
	Imports System.Runtime.InteropServices ' for <ComVisible(True)>
	Imports ExcelDna.Integration.CustomUI ' needed for IRibbonControl
	Imports DNAIRibbonControl = ExcelDna.Integration.CustomUI.IRibbonControl ' needed for IRibbonControl
	Imports Microsoft.Office.Interop
	Imports Microsoft.Office.Core
	Imports Microsoft.Office.Interop.Excel ' Các giao diện như Workbook, Range
	Module Globals

	using System;
	using System.Diagnostics;
	using System.Threading;
	using ExcelDna.Integration;

	namespace TestDisposable
	{
	public static class RtdClock
	{
	[ExcelFunction(Description = "Provides a ticking clock")]

	using ExcelDna.Integration;
	using static System.Math;
	using static ExcelDna.Integration.XlCall;

	public static class TestFunctions
	{
	[ExcelFunction(Description = "Returns the sum of cells in the target (using the used range for performance)", IsMacroType = true)]
	public static object GetUsedSum([ExcelArgument(AllowReference = true)] object target)
	{
	// NOTE: This function will be volatile by default,