Access to Excel C API and/or COM objects in RTD server functions

[Tom Keirl]

Hey all,

I was hoping to access some cell values using GetValue() from an ExcelReference for use as input parameter values for an asynchronous RTD function.

However, whilst in the RTD Server's ConnectData(), (and whilst also in the function wizard if that is relevant), the call to ExcelReference.GetValue() throws an XlCallException: invalid operation. I've since read that using the Excel C API is a no-no during an RTD server function, which has really put the brakes on something I'm currently developing.

What are my options here? Would a COM Range work if I was to pass the xlfReftext in as a string topic parameter? I wouldn't want to go down the route of serialising all the data in the cells as a single string parameter.

Interestingly in the UDF RTD setup routine, a call to XlCall.Excel(XlCall.xlfReftext, *ExcelReference*, true) is also throwing an XlCallException: invalid xlf (which is surprising considering the number of times I have seen examples of its use).

I'm struggling to understand the limitations here, I shall take a look at the examples but if anyone has any sage advice, please share! Otherwise I love using Excel-DNA it is sooooo good :)


Thanks

Tom

[Govert van Drimmelen]

Hi Tom,

The C API (anything to do with ExcelReference or XlCall.XXX) is not available in the RTD routines.

Could you either pass the values into your function, or put the GetValue() in your function wrapper?

The interface to the RTD side is really just an array of strings. COM Ranges are not likely to work any better.

One approach would be to have a global dictionary that you manage, pass a key through to the RTD server as one of the topic strings, and have the RTD server look up the data via the key. You'd still have to do the GetValue() in the wrapper, but at least not have to serialize the data to get it to the RTD server.

-Govert

[Tom Keirl]

Hey Govert,

First off, great work on ExcelDNA!

Regarding your comments, I had considered doing exactly as you suggested, calling GetValue() in the UDF wrapper and stuffing the results somewhere static. I dismissed it though because I had object lifetime concerns. For example, if a workbook was loaded with persisted RTD functions in it, the first opportunity presented by the order of initialisation is not the UDF wrapper being called, but ConnectData() on the RTD server subclass? In such case, the static dictionary as you suggested would not have an opportunity to be populated? Perhaps I am mistaken here?

I appreciate your comment regarding no Excel C API functions being available, likewise for COM objects. Still though, unsure why an XlCall.Excel with xlfReftext would not be throwing an exception in the UDF wrapper, I shall have to investigate this further.

Many thanks for your insight.


Cheers

Tom

[Tom Keirl]

Ooops, that final sentence should read "Still though, unsure why an XlCall.Excel with xlfReftext would be throwing an exception in the UDF wrapper, I shall have to investigate this further."

[Govert van Drimmelen]

Hi Tom,

Your wrapper function might have to be registered as "IsMacroType=true" to allow the xlfReftext call.

Also see: https://exceldna.codeplex.com/discussions/444540

-Govert

[Tom Keirl]

Hey Govert,

Many thanks for pointing me in the right direction. Through bits of research, trial and error, your recommendation of stuffing stuff into a static cache of some sort is going to be the way forward for me.

My concern about the order of initialisation was unfounded, having read that the UDF wrapper is always called for a cell containing an RTD function, and my experiments have shown that it has a reliable lifecycle on saving/loading a spreadsheet & cell content clearing. So I shall construct my static cache in the UDF function for later access in the RTD server implementation. I did note that in order to get the RTD server to produce a reliable value into a cell, I could not mark the UDF wrapper as IsThreadSafe=true. I was also hoping to avoid the IsMacroType=true and IsVolatile=true for performance reasons.

I also ran into a small problem where a UDF function executing GetValue() on an ExcelReference argument containing another UDF was throwing an xlReturnUncalced, because the first UDF was being called before it's dependent reference had been called to calculate (verified using some breakpoints). I believe this is due to the issue you describe of a UDF having to be executed in order to "discover hidden dependencies". I followed your advice you also gave for that issue and ran a XlCall.TryExcel to see whether such a call would be successful, and if not, I terminate the UDF early using a handled exception (into a global exception handler). The UDF actually then executes again in the expected order after the dependent UDF has calculated and GetValue() succeeds on the second pass through, very strange, but it works! This was important because although the functions worked fine with IsMacroType=true or IsThreadSafe=false, I actually need them to be IsMacroType=false and IsThreadSafe=true ... I hope I don't head crashing into another unexpected behaviour, but I believe I've connected enough dots together now to get around these issues!


Many thanks

Tom

[Govert van Drimmelen]

Hi Tom,

Thanks for the feedback on what you've done.

Functions that call (or eventually call) the RTD features cannot be marked as IsThreadSafe=true.

It sounds like you're handling the calculation sequence right when using ExcelReference arguments. I think Excel rearranges the dependency tree based on your (failed) calls to GetValue(), and then calls your function again, but in future calculation will get the order right.

The only other thing I can think of is that IsMacroType=true together with at least one argument marked AllowReference=true will cause the function to be considered as Volatile by Excel automatically (even if you mark the function as IsVolatile=false). If this is not the desired behaviour, you can switch off the volatility inside your function with a call to XlCall.Excel(XlCall.xlfVolatile, false).

You could try to avoid the IsMacroType=true registration by avoiding the xlfRefText call somehow. 

Why do you need it?

Do you have any reason to believe it makes a performance difference?

-Govert

[Tom Keirl]

I was considering the use of xlfRefText to dive into the data I needed through a COM Range object, which you also advised against in the RTD context. I thus abandoned that approach. Suffice to say, I don't think it is required now!

My only concern for performance was the combination of an AllowReferences=true argument and IsMacroType=true function which, as you say, Excel effectively treats it as IsVolatile=true; undesirable for performance. I'll bear what you said about switching xlfVolatile off in the function though. The performance concern was independent of the desire to use xlfRefText.


Many thanks

Tom