[ExcelDna] Setting up a timed call

[baatch]

Hi guys,

I'd posted earlier as I was getting an XlCallException thrown when
trying to call xlcOnTime - I found the problem was because I was
calling Excel from a standard worksheet function, and I am not allowed
to call commands.

In "fixing" this, I have thought of some related questions and would
be extremely grateful for any advice!

In order to avoid the exception, and call Excel under an allowed
context, I decorated the standard worksheet function using
IsMacroType=true. The result of this is that although the call to
XlCall.Excel(XlCall.xlcOnTime, ...) does not cause any exception, it
returns false.

I have tried the following alternatives for the syntax for the
xlcOnTime call, and still get false returned in the isStarted object.

object xlNow = XlCall.Excel(XlCall.xlfNow);
object isStarted = XlCall.Excel(XlCall.xlcOnTime,
(double)xlNow + 1/(24*60*60), "poll");
or,
DateTime now = DateTime.Now;
object isStarted = XlCall.Excel(XlCall.xlcOnTime,
now.AddSeconds(1), "poll");

where "poll" is a command registered using [ExcelCommand(Description =
"Internal Poll", Name = "poll")]


I have also tried replacing the xlcOnTime call with its COM equivalent
and this DOES work, as follows

DateTime now = DateTime.Now;
_applicationObject.OnTime(now.AddSeconds(1), "poll");


The questions are:

1) Is the syntax I am using incorrect for the xlcOnTime call? If so,
what it the correct way to call? If not, what is the problem?

2) What are the side-effects of decorating the UDF with
IsMacroType=true?

3) Is it safe to call the COM OnTime method from a UDF in this way? I
have read elsewhere that this is probably not a good idea.


The eventual purpose of trying to set up a timed call is this:
I have hooked up fucntions in my xll to return an RTD worksheet
function object to excel when called - this is working, and my RTD
server returns a delimited string - in the UDF call the first member
of the delimited string is returned into the calling cell. The timed
call then picks up the rest of the delimited string from a cache and
outputs this delimited string as an array over multiple cells below
the original caller, which is also working, but at the moment only via
the COM OnTime method.
So, my fourth and final question!

4) Is there a better way that someone could outline to try to achieve
the above, maybe using the RTD server itself for example? I have tried
(naively) using XlCall.Excel(XlCall.xlcOnTime, now.AddSeconds(1),
"poll") from within the RefreshData method called by Excel, but
probably obviously to everyone else this didn't work. I can only guess
that it has something to do with the RTD server not having any excel
object handles and/or it being a COM interface rather than Excel C
API.


Thanks Govert for ExcelDna.. it rocks... :)








--
You received this message because you are subscribed to the Google Groups "ExcelDna" group.
To post to this group, send email to exce...@googlegroups.com.
To unsubscribe from this group, send email to exceldna+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/exceldna?hl=en.

[rleth...@gmail.com]

Forgive me if I've misunderstood your problem, but let me jump
straight in to your paragraph between points (3) and (4).

If your RTD server returns a delimited string then you could just
split this string using worksheet functions in a cell in the
spreadsheet itself (which has the advantage of maintaining Excel's
dependency tree). Of course you can also write a customised parser in
ExcelDna as a WorksheetFunction that will operate on the delimited
string and return an array of cells.

Or, if you are in control of the RTD server, you could send each
individual property as a separate "topic", the syntax of the RTD
function is =RTD(ProgID, Server, Topic1, Topic2, ... up to Topic27) so
each element of your delimited string could be a different Topic2,
rather than being one big Topic1? For instance if you have an object
property Circle.Area then that would most logically map to
=RTD(ProgID, Server, "Circle", "Area").

[baatch]

Hi there,

thanks for your reply!

I see what you mean, but i'm doing that multi-cell output from a
single caller with the worksheet function in it.

So currently, I return the first element of the delimited string as a
result of the call to the worksheet function into the calling cell.
Then I output the rest of the elements below the calling cell, but
those cells do not have any functions in them - i'm just setting the
value of those cells directly using xlSet, hence the need for a
separate worksheet function and command operation, one for the first
value and one for the rest of the elements respectively.

My problem is that I would like to do this using C API xlcOnTime,
instead of using COM _applicationObject.OnTime, but I can't set the
xlcOnTime - it always returns false, so i'm not sure if it's the
syntax i'm using, or whether the logic of calling xlcOnTime from a
macro-type worksheet function that is the problem?

The reason I want to use xlcOnTime instead, is that
_applicationObject.OnTime fails sometimes when excel is busy. As far
as I can see xlcOnTime never fails, for example if I set up the timed
call from Auto_Open using IExcelAddin interface.

I hope that makes it a bit clearer! thanks!

[Govert van Drimmelen]

Hi,

Let me add a first few comments, then I'll try to answer your
questions.

Firstly - what you are trying to do is clearly pushing against the
functional way that spreadsheet calculations are designed, and so it's
not surprising that you bump into funny problems. However, trying to
update the sheet in various ways from a function is quite a common
request, and I think add-ins like that from Bloomberg do this too. A
similar situation is if you are returning data from a query, where the
number of rows is unknown when the query is run. I think there have
been some attempts to support this in recent versions of Excel with
features like data tables, though I have no experience with this.

Secondly - only do the scheduled macro call if the size of the calling
range is smaller than the size of the results you want to send back,
and then in the macro call do not do xlSet with the values, but rather
extend the array formula to the enlarged range (which also forces a re-
calc, no returning to the right size range). You'd still have to go
through all the hoops to get into a context where you can set the
formula in the range, but future recalculations would not have to re-
do this. Your function would return an array of the right size and
need no funny work in most cases. This aligns much better with Excel's
recalculation, and limits the number of macro run/set events going on.

Thirdly - thank you very much for the detailed write-up of what you
are finding. I'm sure many others will find it useful to see what you
are trying. Keep it coming...


Now for more details on your questions:

1. You syntax for xlcOnTime is correct, and indeed it seems to fail
when called from a UDF as you have found. You can check that you are
making the call correctly, by putting the call into a macro that you
run from a menu - in this case your scheduled call-back should work
fine. I think there are some examples of where I tried this in the
MoreSamples.dna file.

2. Marking a function with IsMacroType=true changes the registration
string that ExcelDna sends to Excel - it adds a # to the string
denoting a "macro sheet equivalent function". Such functions are in
Class 2 and can call various Excel information functions "that return
a value but perform no action that affects the workspace or any open
workbook". (some details here: http://msdn.microsoft.com/en-us/library/bb687835(v=office.12).aspx
and if you search for xlfRegister). IsMacroType=true functions still
cannot call xlSet, or change other parts of the sheet, as you have
discovered.

3. I don't know if it is safe to call Application.OnTime from a UDF
context, but my guess is that you are OK. I'd certainly like to know
if you find that this causes any crashes. Certainly trying to be more
ambitious in your calls to the automation interface during UDF calls
can be dangerous - it just seems like Application.OnTime would not do
work that interferes with the calculation path.

As for the RTD calls, I think a call to XlCall.Excel(XlCall.xlfRtd...)
from inside your UDF will essentially make your function get
recomputed like an RTD function - so you get to expose a nice name
without the topic stuff to your users and get async updates from those
cells through the RTD mechanism. I think you can also use the hidden
names feature to set up behind-the-scenes names that get updated from
RTD formulae - this might be useful for setting up RTD calls that do
not affect your sheet.

4. Other ways to do this, and avoiding Application.OnTime? The old-
fashioned way to do this is explained well in Steve Dalton's book on
building XLL's, called "Financial Applications Using Excel Add-in
Development in C/C++". His plan is to set up a polling mechanism using
xlcOnTime, which checks periodically whether any async work needs to
be done. Your UDF would then set some flags or data for processing on
the next OnTime macro call, and the OnTime macro would also re-
schedule itself to run again later. As you have noticed, the xlcOnTime
calls are more reliable in happening even if Excel is in a funny state
than Application.OnTime, something he also mentions.

There are some problems with this - when the Application.OnTime or
xlcOnTime macro fires, some of the Excel state is reset, for example
the Cut/Copy selection is lost. This can be quite irritating to users,
so it is suggested that you add a menu option to disable the polling.


If you find Application.OnTime safe (Excel doesn't crash) but
unreliable (the call is sometimes forgotten) you might set a Windows
timer to call Application.OnTime periodically, until the work is done.




You might like to look around the Excel SDK newsgroup
(microsoft.public.excel.sdk - http://groups.google.com/group/microsoft.public.excel.sdk)
archives for some more ideas, or even re-post your question there. It
has not been very active lately, but in the past there have been some
really detailed discussions on this kind of thing, from people who
have good experience pushing the limits of Excel.

If you get a nice plan that works, please let us know. Maybe we could
wrap it into an ExcelDnaUtil.CallMacro(...) helper that sets
everything up and gets called in a macro context, or even an
ExtendRange=true flag on the function attribute to do this.


Please let us know what you find.

Regards,
Govert

[Govert van Drimmelen]

Hi,

Another idea - maybe you can register to get calculation events
(XlCall.xlcOnRecalc). Then you'll be called right after your UDF
returns in a macro context, where you can do stuff like change the
formulae to an extended range.

--Govert--

> (microsoft.public.excel.sdk  -http://groups.google.com/group/microsoft.public.excel.sdk)

[baatch]

Hi Govert!

thanks for your response. Sorry for my delayed one, I have been away

I am investigating all suggested avenues and testing.

I shall post the results this week.

thanks again!
Javier