govert/RTD DisconnectData array issue.md

## RTD DisconnectData array issue.md

      
    Raw
  

              RTD DisconnectData array issue.md
            
          
    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.
When the same function is used from a single-cell formula, the same sequence of calls does cause the correct DisconnectData call to be made against the RTD server.
Impact

Excel's RTD mechanism, with calls effectively embedded in user-defined functions, is widely used to implement asynchronous functions streaming data services, and handle tracking features to Excel. All of these rely on the correct working of the RTD implementation, including correct topic Disconnect notifications.
Currently these features cannot reliably be used when called from array formulas, due to the issue presented here.
Steps to reproduce

The tests were done with Excel 2013 v. 15.0.4771.1000 (32-bit) and Visual Studio 2015 Update 1 on Windows 10.
Create a minimal RTD server in C#


Start Visual Studio "As Administrator" (to enable the COM registration - see below).
Create a new Class Library project.
In the project properties, on the Build tab, enable "Register for COM interop". (Building with this setting on requires admin permissions, so Visual Studio should be run "As Administrator" if the build fails with an "access denied" error.)
In the project properties, on the Debug tab, set the Start Action to be "Start external program" with Excel as the program (on my machine, the path to Excel is "C:\Program Files (x86)\Microsoft Office\Office15\EXCEL.EXE").
Add a Reference to the Microsoft.Office.Interop.Excel assembly.
Add the following code in the .cs file:

using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Runtime.InteropServices;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;

namespace MinimalRtd
{
    [
        ComVisible(true),
        Guid("86541CE9-EA39-4175-B37A-FDAE655AD30C"),
        ProgId("Minimal.RtdServer"),
    ]
    public class RtdServer : IRtdServer
    {
        private IRTDUpdateEvent _callback;
        private Timer _timer;
        private List<int> _topicIds;

        public int ServerStart(IRTDUpdateEvent callback)
        {
            Log("ServerStart");
            _callback = callback;

            _topicIds = new List<int>();

            _timer = new Timer();
            _timer.Tick += new EventHandler(TimerEventHandler);
            _timer.Interval = 1000;
            _timer.Start();

            return 1;
        }

        public void ServerTerminate()
        {
            Log("ServerTerminate");
            _timer.Dispose();
            _timer = null;
        }

        public object ConnectData(int topicId, ref Array strings, ref bool newValues)
        {
            Log("ConnectData: {0}, {1}", topicId, strings.GetValue(0));
            _topicIds.Add(topicId);
            return GetTime();
        }

        public void DisconnectData(int topicId)
        {
            Log("DisconnectData: {0}", topicId);
        }

        // Because we're using a System.Windows.Forms.Timer that was created on the main thread, 
        // this call will always be on the main thread.
        private void TimerEventHandler(object sender, EventArgs args)
        {
            _callback.UpdateNotify();
        }

        // All topics are updated, and get the same value
        public Array RefreshData(ref int topicCount)
        {
            string time = GetTime();

            topicCount = _topicIds.Count;
            object[,] data = new object[2, topicCount];
            for (int i = 0; i < topicCount; i++)
            {
                data[0, i] = _topicIds[i];
                data[1, i] = time;
            }
            return data;
        }

        public int Heartbeat()
        {
            return 1;
        }

        private string GetTime()
        {
            return DateTime.Now.ToString("hh:mm:ss:ff");
        }

        private void Log(string format, params object[] args)
        {
            Debug.Print(format, args);
        }
    }
}

In the project properties, on the Debug tab, Set Excel as the star
Press F5 to build and start Excel.

Test the RTD call directly in Excel


In a new Workbook, enter the formula: =RTD("Minimal.RtdServer", "", "test")
The result should be a ticking time string, updated every 2 seconds (the default Application.RTD.ThrottleInterval is 2000).
Check the Output window in Visual Studio for log strings
Change the topic string (the "test" argument) in the formula: =RTD("Minimal.RtdServer", "", "test222") and note the debug output with the ConnectTopic for the new topic, followed by the DisconnectTopic call for the old topic.
Finally delete the formula, which diconnects the topic and terminates the server.
My debug out was:

ServerStart
ConnectData: 0, test
ConnectData: 1, test2
DisconnectData: 0
DisconnectData: 1
ServerTerminate

Add a VBA wrapper function


Open the VBA editor
Insert a new module
Add the following code:

Function RtdWrapper(topic As String, live As Boolean) As Variant

    If Not live Then
        RtdWrapper = "OFF"
        Exit Function
    End If
    
    Dim val As Variant
    val = Application.WorksheetFunction.rtd("Minimal.RtdServer", "", topic)
    
    ' Return a 2x1 array, with the RTD result just repeated
    Dim result(0 To 1, 0 To 0) As Variant
    result(0, 0) = "0:" & val
    result(1, 0) = "1:" & val
    
    RtdWrapper = result
End Function
Test the wrapper with a single-cell formula

(Still running under the debugger, to see the output messages)


Insert into Excel the following:


A1: test


A2: 1


A3: =RtdWrapper(A1, A2)


The RTD server should start, topic connect, and ticking time displayed.


Now change the value of A2 to 0.


The result in A3 changes to "OFF" and the topic is disconnected (since the wrapper does not make the RTD call). THIS IS THE CORRECT BEHAVIOUR


The debugger Output window shows:


ServerStart
ConnectData: 0, test
DisconnectData: 0
ServerTerminate 

Test the wrapper with an array formula


Insert into Excel the following:


A1: test


A2: 1


A3: {=RtdWrapper(A1, A2)}


A4: {=RtdWrapper(A1, A2)}


Note that A3 and A4 should be entered as an array formula, by selecting both cells, entering the formula and then pressing Ctrl+Shift+Enter.


Excel should display:


A1: test


A2: 1


A3: 0:12:30:06:46


A4: 1:12:30:06:46


Now change cell A2 to 0.


The result in A3 changes to "OFF" and the topic is NOT disconnected (since the wrapper does not make the RTD call). THIS IS THE PROBLEM


We expect the RTD topic to be disconnected at this point.


Instead we just have


ServerStart
ConnectData: 0, test