Trying to build Excel RTD server in Delphi

Question

I'm trying to build an RTD server for Excel in Delphi and I cannot get this part of the code to work:

function TRtdServer.RefreshData(var TopicCount: Integer): PSafeArray;
//Called when Excel is requesting a refresh on topics. RefreshData will be called
//after an UpdateNotify has been issued by the server. This event should:
//- supply a value for TopicCount (number of topics to update)
//- The data returned to Excel is an Object containing a two-dimensional array.
//  The first dimension represents the list of topic IDs.
//  The second dimension represents the values associated with the topic IDs.
var
  Data : OleVariant;
begin
   //Create an array to return the topics and their values
   //note:The Bounds parameter must contain an even number of values, where each pair of values specifies the upper and lower bounds of one dimension of the array.
   Data:=VarArrayCreate([0, 1, 0, 0], VT_VARIANT);
   Data[0,0]:=MyTopicId;
   Data[1,0]:=GetTime();
   if Main.Form1.CheckBoxExtraInfo.Checked then Main.Form1.ListBoxInfo.Items.Add('Excel called RefreshData. Returning TopicId: '+IntToStr(Data[0,0])+' and Value: '+Data[1,0]);
   TopicCount:=1;
//   RefreshTimer.Enabled:=true;
   //Result:=PSafeArray(VarArrayAsPSafeArray(Data));
   Result:=PSafeArray(TVarData(Data).VArray);
end;

I'm not sure about this part:

Result:=PSafeArray(TVarData(Data).VArray);

But it could be any part of the code. Excel just doesn't show any result in the cell containing the rtd() function call. I did manage to get a result into the cell the first time Excel calls my "ConnectData" function that simple returns a string instead of a PSafeArray (although the very first call to that function fails to produce a result (N/A). Only after changing the Topic in the RTD() call it displays a result (one time only))

I based the code on an example in C# from https://blog.learningtree.com/excel-creating-rtd-server-c/

Can anyone point me in the right direction?

Answer 1

OleVariant owns the data it holds, and will release that data when itself goes out of scope. So you are returning an invalid PSafeArray pointer to Excel. You need to either:

1. release ownership of the array pointer before returning it:

   function TRtdServer.RefreshData(var TopicCount: Integer): PSafeArray;
   var
     Data : OleVariant;
   begin
     ...
     Result := PSafeArray(TVarData(Data).VArray);
     TVarData(Data).VArray = nil; // <-- add this
   end;
   

2. use SafeArrayCopy() to make a copy of the array, and then return the copy:

   uses
      ..., ActiveX;
   
   function TRtdServer.RefreshData(var TopicCount: Integer): PSafeArray;
   var
     Data : OleVariant;
   begin
     ...
     OleCheck(
       SafeArrayCopy(
         PSafeArray(TVarData(Data).VArray),
         Result
       )
     );
   end;