UDF always recalculates when it wraps a RTD call (single cell)

[ExcelUser]

Hello,

I have an issue where I wrap an XlCall.RTD call in a UDF and everytime the cell in question is refreshed by the RTD, it calls the UDF again.

The function is  "public static object TestRtd() 

{

return XlCall.RTD("TestRtdServer", null,newTopic);

}".

Calculation mode is automatic.

I tried setting the function as Volatile=false but to no avail.

Is this intended behavior? I am doing data validation in the UDF which I do not want to do everytime the cell is refreshed.

Thanks

[Govert van Drimmelen]

Hi,

This is expected for any kind of RTD wrapper. The wrapper could potentially be changing or processing the return value from the RTD call.

I guess you could try to push the validation into the ConnectData of your RTD server, or else cache the expensive work somehow.

-Govert

[ExcelUser]

Thank you for the prompt reply.

There is no way to avoid this behavior, even with a standard COM RTD server and VBA function? (I do not wish to do this but just wondering).

Also, in connect data, is there a way to "reject" the topic if the validation fails? (I should have a look at the underlying implementation, i dont want "wrong" topics accumulating in the ExcelRtdServer)

[Govert van Drimmelen]

Hi,

Wrapper functions around RTD would work the same in this regard, whether you implementing in VBA, C++ or .NET with Excel-DNA.

You can't 'reject' the RTD topic from ConnectData, but I guess you can set the result to some error value. The topic would only exist until the formula calling it is changed, and RTD topics are pretty light-weight, so I'm not sure I'd be worried about them accumulating. Still, I guess if you expect to have a million error topics on a sheet, you might want to test a bit before taking that approach....

How expensive is your parameter validation? You keep keep a dictionary or something of 'valid parameter sets' which you add to in ConnectData and clean up in DisconnectData. Then first look up whether your parameter combination is 'valid' before doing the more expensive validation check.

-Govert

---

From: exce...@googlegroups.com [exce...@googlegroups.com] on behalf of ExcelUser [jjb...@gmail.com]
Sent: 30 April 2014 11:52 PM
To: exce...@googlegroups.com
Subject: [ExcelDna] Re: UDF always recalculates when it wraps a RTD call (single cell)

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To post to this group, send email to exce...@googlegroups.com.
Visit this group at http://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.

[ExcelUser]

Hi,

The validation is not that bad (I already cache), but I thought it kind of defeats the RTD purpose to have the udf called everytime when I just want excel to pull the data from the server.

Thank you for the answer, and for creating such a well made library.

To post to this group, send email to exc...@googlegroups.com.

[Govert van Drimmelen]

Hi,

The main purpose of RTD is to allow you to push data into Excel - there is no other reasonable mechanism to tell Excel to update a cell. Using RTD, the cells can recalculate exactly when there are changes in the data.

The overhead of a function call to the wrapper function should be tiny, but the only way to avoid that is to have the RTD call directly in the formula, which won't help your scenario at all.

The wrapper function might not be returning exactly the value set for the topic in the RTD server (and in the case of ExcelRtdServer it doesn't).

I guess if you had to manage many millions of cells updating at sub-second rates, it might be worth considering a C-based wrapper function, which could avoid some of the string marshaling and the overhead of the managed transition.

-Govert

---

From: exce...@googlegroups.com [exce...@googlegroups.com] on behalf of ExcelUser [jjb...@gmail.com]

Sent: 01 May 2014 08:53 AM
To: exce...@googlegroups.com
Subject: Re: [ExcelDna] Re: UDF always recalculates when it wraps a RTD call (single cell)

To post to this group, send email to exce...@googlegroups.com.

[ExcelUser]

I do not plan on having millions of cells, so hopefully I should be perfectly fine.