256 char limit on RTD
905 views
Skip to first unread message
Serge
Dec 16, 2010, 11:28:41 AM12/16/10
to Excel-Dna
There seems to be a 256 character limit on returned strings from RTD
calls through ExcelDNA, using both XlCall.RTD() and
XlCall.Excel(XlCall.xlfRtd,...). An RTD called directly in Excel has
no character limit. Any ideas on an elegant solution for this?
Thanks!
calls through ExcelDNA, using both XlCall.RTD() and
XlCall.Excel(XlCall.xlfRtd,...). An RTD called directly in Excel has
no character limit. Any ideas on an elegant solution for this?
Thanks!
Govert van Drimmelen
Dec 16, 2010, 4:27:30 PM12/16/10
to Excel-Dna
Hi Serge,
You don't say which Excel version you are using - I think you are
seeing a limit of Excel-Dna strings under Excel 2003 and older.
This is also a limit for regular Excel-Dna functions returning strings
on the older Excel versions, and nothing specific to the RTD wrapper.
Under the older versions, the C API that Excel-Dna uses has a 256
character limit. Regular RTD servers work directly through a COM
interface, so you might not have such a restriction.
Since Excel 2007 the limit is about 16k characters, and strings are
Unicode.
I know of no work-around.
Regards,
Govert
You don't say which Excel version you are using - I think you are
seeing a limit of Excel-Dna strings under Excel 2003 and older.
This is also a limit for regular Excel-Dna functions returning strings
on the older Excel versions, and nothing specific to the RTD wrapper.
Under the older versions, the C API that Excel-Dna uses has a 256
character limit. Regular RTD servers work directly through a COM
interface, so you might not have such a restriction.
Since Excel 2007 the limit is about 16k characters, and strings are
Unicode.
I know of no work-around.
Regards,
Govert
Serge
Dec 16, 2010, 9:43:18 PM12/16/10
to Excel-Dna
Thanks, Govert. I'm using Excel 2007. I tried both XlCall.RTD() and
XlCall.Excel(XlCall.xlfRtd,...) -- how else could I call a regular RTD
server in ExcelDNA? I haven't confirmed this behavior in non-RTD
functions. Can you confirm that you have gotten RTD to return longer
than a 256 character string in RefreshData() to a ExcelDNA UDF?
My temporary workaround is to store the full string in a static class
and just return a pointer via RTD RefreshData(). This works, but
seems to disrupt DisconnectData(), so a more elegant solution would be
nice.
Serge
XlCall.Excel(XlCall.xlfRtd,...) -- how else could I call a regular RTD
server in ExcelDNA? I haven't confirmed this behavior in non-RTD
functions. Can you confirm that you have gotten RTD to return longer
than a 256 character string in RefreshData() to a ExcelDNA UDF?
My temporary workaround is to store the full string in a static class
and just return a pointer via RTD RefreshData(). This works, but
seems to disrupt DisconnectData(), so a more elegant solution would be
nice.
Serge
Govert van Drimmelen
Dec 17, 2010, 2:07:23 AM12/17/10
to Excel-Dna
Mmmm....
OK - I'll try to have a closer look over the weekend.
I've never tried anything like this before.
Under the hood, XlCall.RTD() calls XlCall.Excel(XlCall.xlfRtd,...), so
they're really the same in this regard.
Since you're already doing the RTD registration elsewhere, you don't
really need the wrapper part the XlCall.RTD gives (it sets up the
registration before the XlCall.xlfRtd call).
Maybe there is some Excel restriction in the xlfRtd call?
An alternative would be to call
Application.WorksheetFunctions.RTD(....), from within your function.
Using the COM interface from within user-defined functions is not
advised, but you could give it a try. In this case it's probably best
to mark your registered function as IsMacroType=true.
-Govert
OK - I'll try to have a closer look over the weekend.
I've never tried anything like this before.
Under the hood, XlCall.RTD() calls XlCall.Excel(XlCall.xlfRtd,...), so
they're really the same in this regard.
Since you're already doing the RTD registration elsewhere, you don't
really need the wrapper part the XlCall.RTD gives (it sets up the
registration before the XlCall.xlfRtd call).
Maybe there is some Excel restriction in the xlfRtd call?
An alternative would be to call
Application.WorksheetFunctions.RTD(....), from within your function.
Using the COM interface from within user-defined functions is not
advised, but you could give it a try. In this case it's probably best
to mark your registered function as IsMacroType=true.
-Govert
Serge
Dec 17, 2010, 11:50:30 AM12/17/10
to Excel-Dna
Thanks, Govert!! I can confirm that going through the COM interface
removes the 256-character restriction. However, performance isn't
nearly as good as XlCall.RTD(), so I'll stick with the workaround I
mentioned above. Let me know if you want me to test anything that
might remove the 256-character limit on XlCalc.RTD().
I'm getting some strange performance issues with XlCalc.RTD as well.
Let me explain:
I'm trying to pass a string back from RTD, explode it in the UDF and
return it as an array. I have IsMacroType set to true because
otherwise the UDF returns #VALUE if the selection is an array. It is
extremely responsive when the selection range for the UDF is under
5-6k cells large. The size of the RTD string and returned array
doesn't matter, so performance is just as fast if the RTD/UDF is
returning a 20k-cell array or a 2k-cell array. However, when I select
a 12k-cell output array for the UDF (2 columns, 6k rows), CPU usage
spikes to 100% for 5-10 seconds. Again, it doesn't matter how big the
returned array is -- only how big the selected output for the UDF is.
So I could be returning a 1-character string or a 2x2 array of strings
and putting it into a 2x6k selection range, and it will hang for 5-10
seconds. The closest I can track down the performance issue is to
between when RefreshData returns the topic to Excel and the UDF is
called. What is also interesting is if I return a 12k-cell array into
a single cell selection, it is blazing fast. Then if I enlarge the
selection to 12k cells (hit F2 on the cell with the UDF, enlarge the
selection to 12k cells, then hit cntl-shift-enter), it is also blazing
fast. However, going straight to the 12k selection, I get the 5-10
hang. And, either way, if the UDF has to be recalculated, I get the
performance hit.
The only time I get this odd performance problem is when XlCalc.RTD()
is in the UDF. I can return a 100k cell array into a 100k selection
without a problem if there is no RTD call in the UDF. This, and the
other tests I have done, makes me think that this is an issue with how
Excel triggers a wrapper UDF call after RefreshData() returns results
for a topic tied to the UDF. It looks like Excel is doing some
calculations that depend on the size of the selection of the UDF. I
have no idea how to dig deeper into the process, though.
A hack I have experimented with involves using a macro like Ozzie's
TidyArray to make the UDF's selection range smaller when the UDF is
first called, and then expand it after the result are set. This is
really ugly but actually works very well for when the UDF is first
created. However, if the UDF needs to re-run because its inputs
change, it's hard to shrink it before RefreshData() is called in a
consistent way.
Let me know if you have any ideas on the RTD->array performance issue,
and if you want me to run any tests on the 256-character restriction
issue.
Thanks a lot for the help!
Best,
Serge
removes the 256-character restriction. However, performance isn't
nearly as good as XlCall.RTD(), so I'll stick with the workaround I
mentioned above. Let me know if you want me to test anything that
might remove the 256-character limit on XlCalc.RTD().
I'm getting some strange performance issues with XlCalc.RTD as well.
Let me explain:
I'm trying to pass a string back from RTD, explode it in the UDF and
return it as an array. I have IsMacroType set to true because
otherwise the UDF returns #VALUE if the selection is an array. It is
extremely responsive when the selection range for the UDF is under
5-6k cells large. The size of the RTD string and returned array
doesn't matter, so performance is just as fast if the RTD/UDF is
returning a 20k-cell array or a 2k-cell array. However, when I select
a 12k-cell output array for the UDF (2 columns, 6k rows), CPU usage
spikes to 100% for 5-10 seconds. Again, it doesn't matter how big the
returned array is -- only how big the selected output for the UDF is.
So I could be returning a 1-character string or a 2x2 array of strings
and putting it into a 2x6k selection range, and it will hang for 5-10
seconds. The closest I can track down the performance issue is to
between when RefreshData returns the topic to Excel and the UDF is
called. What is also interesting is if I return a 12k-cell array into
a single cell selection, it is blazing fast. Then if I enlarge the
selection to 12k cells (hit F2 on the cell with the UDF, enlarge the
selection to 12k cells, then hit cntl-shift-enter), it is also blazing
fast. However, going straight to the 12k selection, I get the 5-10
hang. And, either way, if the UDF has to be recalculated, I get the
performance hit.
The only time I get this odd performance problem is when XlCalc.RTD()
is in the UDF. I can return a 100k cell array into a 100k selection
without a problem if there is no RTD call in the UDF. This, and the
other tests I have done, makes me think that this is an issue with how
Excel triggers a wrapper UDF call after RefreshData() returns results
for a topic tied to the UDF. It looks like Excel is doing some
calculations that depend on the size of the selection of the UDF. I
have no idea how to dig deeper into the process, though.
A hack I have experimented with involves using a macro like Ozzie's
TidyArray to make the UDF's selection range smaller when the UDF is
first called, and then expand it after the result are set. This is
really ugly but actually works very well for when the UDF is first
created. However, if the UDF needs to re-run because its inputs
change, it's hard to shrink it before RefreshData() is called in a
consistent way.
Let me know if you have any ideas on the RTD->array performance issue,
and if you want me to run any tests on the 256-character restriction
issue.
Thanks a lot for the help!
Best,
Serge
Govert van Drimmelen
Dec 17, 2010, 6:18:35 PM12/17/10
to Excel-Dna
Hi Serge,
That you very much for the detailed write-up. It certainly sounds like
you're pushing the envelope on this, which is awesome!
My first thought is to refactor your result so that you are many
formulae in the many cells, fed from the same RTD server. That should
be the scenario that Excel is optimised for. As you've noticed, RTD is
not really meant for these (large) array formulae, so I can understand
there being some internal Excel structure that needs to keep a mapping
from cells to RTD topics, which could have funny complexity when it
has to keep track of big blocks of cells.....
Maybe you can try something with a defined name? You define a name
that evaluates the RTD-wrapping function, and then have the array
formula depend on the name. I imagine you get some kind of indirection
that way.
Or you could have a single cell that calls the RTD function, and then
have the array formula depend on the single cell so it is recomputed
at the right time, but the array formula actually retrieves the data
from the internal cache?
Govert
That you very much for the detailed write-up. It certainly sounds like
you're pushing the envelope on this, which is awesome!
My first thought is to refactor your result so that you are many
formulae in the many cells, fed from the same RTD server. That should
be the scenario that Excel is optimised for. As you've noticed, RTD is
not really meant for these (large) array formulae, so I can understand
there being some internal Excel structure that needs to keep a mapping
from cells to RTD topics, which could have funny complexity when it
has to keep track of big blocks of cells.....
Maybe you can try something with a defined name? You define a name
that evaluates the RTD-wrapping function, and then have the array
formula depend on the name. I imagine you get some kind of indirection
that way.
Or you could have a single cell that calls the RTD function, and then
have the array formula depend on the single cell so it is recomputed
at the right time, but the array formula actually retrieves the data
from the internal cache?
Govert
Serge
Dec 17, 2010, 7:10:12 PM12/17/10
to Excel-Dna
Thanks, Govert.
I'll try to do some tests later on to see the tradeoff between number
of RTD calls and cells affected by each RTD. Two calls of 6k cells
each will perform better than one call of 12k cells, but based on my
testing I also think 6k calls of 2 cells each will perform better than
12k calls of one cells each. Either way, what's most important is how
the user wants to use Excel, which is definitely 1 UDF returns 12k
cells.
The other two options sound interesting, and I'd like to try them out.
1. What's a defined name? I haven't heard of the concept. Maybe you
can point me to some code in ExcelDNA or some other docs?
2. How do I make an array UDF depend on the single cell UDF? This
sounds like it would do the trick. I suppose once I figure out how to
make one dependent on the other the difficulty will be making the two
calls seamless to the user. After all, the user will except the array
to start wherever the initial formula is typed in. I'm happy to
experiment with this.
Serge
I'll try to do some tests later on to see the tradeoff between number
of RTD calls and cells affected by each RTD. Two calls of 6k cells
each will perform better than one call of 12k cells, but based on my
testing I also think 6k calls of 2 cells each will perform better than
12k calls of one cells each. Either way, what's most important is how
the user wants to use Excel, which is definitely 1 UDF returns 12k
cells.
The other two options sound interesting, and I'd like to try them out.
1. What's a defined name? I haven't heard of the concept. Maybe you
can point me to some code in ExcelDNA or some other docs?
2. How do I make an array UDF depend on the single cell UDF? This
sounds like it would do the trick. I suppose once I figure out how to
make one dependent on the other the difficulty will be making the two
calls seamless to the user. After all, the user will except the array
to start wherever the initial formula is typed in. I'm happy to
experiment with this.
Serge
Govert van Drimmelen
Dec 17, 2010, 7:35:29 PM12/17/10
to Excel-Dna
Hi Serge,
1. Defined Names are an important tool in Excel that I know nearly
nothing about. This looks like a good start on defined names:
http://www.cpearson.com/excel/DefinedNames.aspx.
You can assign a name to a range in the sheet, or to a formula. There
is a Name Manager under the Formulas tab.
So you can make a new name, say "MyName", and set it to "Refer to:"
something like "=WhatTimeIsIt()" which is an RTD formula.
Then in a cell you can put "=MyName" and it will do what you expect.
Now I'm thinking you can also read the value of the name using
XlCall.xlfEvaluate, so maybe that way you can get the indirection to
make the big arrays work.
Related to defined names are the following:
* xlfGetName to get the definition of a name,
* xlfGetDef to get the name from a reference,
* xlcDefinedName, xlcDeleteName, xlfSetName, xlfNames.
2. I mean an explicit reference: a single cell (A1) with
=MyRTDChangeNotify() and a large array with =MyRTDValues(A1). Then the
large array will know to update from the in-memory store when the
trigger cell A1 changes. If this is fast and efficient, maybe you can
hide the A1 cell and the dependency in a defined name.
Govert
1. Defined Names are an important tool in Excel that I know nearly
nothing about. This looks like a good start on defined names:
http://www.cpearson.com/excel/DefinedNames.aspx.
You can assign a name to a range in the sheet, or to a formula. There
is a Name Manager under the Formulas tab.
So you can make a new name, say "MyName", and set it to "Refer to:"
something like "=WhatTimeIsIt()" which is an RTD formula.
Then in a cell you can put "=MyName" and it will do what you expect.
Now I'm thinking you can also read the value of the name using
XlCall.xlfEvaluate, so maybe that way you can get the indirection to
make the big arrays work.
Related to defined names are the following:
* xlfGetName to get the definition of a name,
* xlfGetDef to get the name from a reference,
* xlcDefinedName, xlcDeleteName, xlfSetName, xlfNames.
2. I mean an explicit reference: a single cell (A1) with
=MyRTDChangeNotify() and a large array with =MyRTDValues(A1). Then the
large array will know to update from the in-memory store when the
trigger cell A1 changes. If this is fast and efficient, maybe you can
hide the A1 cell and the dependency in a defined name.
Govert
Serge
Dec 17, 2010, 9:29:38 PM12/17/10
to Excel-Dna
Awesome!!
#2 worked perfectly:
I'm doing =dump(getCachedId(a,b,c))
-getCachedId() calls the RTD wrapper and returns the topicID. The
topicID points to an internal data structure that contains the full
string
-dump() looks up the full string by the ID and dumps it into an array
I can do 40k cells without any movement in the CPU this way.
The next and final step will be to make this seamless to the user.
The user should just call GetDataAndReturnArray(a,b,c) and not have to
worry about the dump function. I'll do some research on defined names
and see if that can help. Thanks for the link and xlc functions.
The result of these efforts will be a very nice way to get huge arrays
out of RTD in pretty much real time. Thanks for all your help!
Serge
> ...
>
> read more »
#2 worked perfectly:
I'm doing =dump(getCachedId(a,b,c))
-getCachedId() calls the RTD wrapper and returns the topicID. The
topicID points to an internal data structure that contains the full
string
-dump() looks up the full string by the ID and dumps it into an array
I can do 40k cells without any movement in the CPU this way.
The next and final step will be to make this seamless to the user.
The user should just call GetDataAndReturnArray(a,b,c) and not have to
worry about the dump function. I'll do some research on defined names
and see if that can help. Thanks for the link and xlc functions.
The result of these efforts will be a very nice way to get huge arrays
out of RTD in pretty much real time. Thanks for all your help!
Serge
>
> read more »
hari
Jul 1, 2011, 1:55:28 AM7/1/11
to exce...@googlegroups.com
Hi All,
I have an xll that exposes couple of udfs. These udfs call RTD using Excel12v with xlfrtd.
whenever I cal Excel12(xlfrtd, arg1...arg41) --> this is failing (if args > 40)
and if one of the argument is string and if its length is greaterr than 255 chars, Excel12* call is failing.
Is it the intended behaviour or bug in excel rtd code?
Even I tried calling RTD from excel and even that is failing if args >40 / any one of the argument length is greater than 255
char. I was trying to find info regarding this and msdn/google is not helpful. This is the only closest info I got..
any info is really appreciated....
thanks,
Hari
Govert van Drimmelen
Jul 1, 2011, 5:23:14 AM7/1/11
to Excel-DNA
Hi Hari,
If you are running on Excel 2010, I suggest you try the newly released
SP1 which I think includes some RTD fixes. (Not sure whether your
issues are covered though.)
Otherwise I suggest you try the Excel for Developers forum -
http://social.msdn.microsoft.com/Forums/is/exceldev. Issues there do
sometimes seem to get escalated to the Excel team.
Regards,
Govert
If you are running on Excel 2010, I suggest you try the newly released
SP1 which I think includes some RTD fixes. (Not sure whether your
issues are covered though.)
Otherwise I suggest you try the Excel for Developers forum -
http://social.msdn.microsoft.com/Forums/is/exceldev. Issues there do
sometimes seem to get escalated to the Excel team.
Regards,
Govert
daman
Jun 8, 2017, 10:44:59 AM6/8/17
to Excel-DNA
I have also noticed the same issue where XlCall.RTD returns string truncated into 255 characters and it's done by object FixValue(object value) in Topic Class(https://github.com/Excel-DNA/ExcelDna/blob/master/Source/ExcelDna.Integration/ExcelRtdServer.cs).Has anyone found a workaround on this?
Btw, I'm using Excel 2013 32bit
Thanks.
Govert van Drimmelen
Jun 8, 2017, 10:58:50 AM6/8/17
to exce...@googlegroups.com
You can use the ExceObserver/Observable wrapper to work around this.
See the RtdClocks part of the Samples repository.
-Govert
GOT2019
Apr 30, 2019, 12:59:00 PM4/30/19
to Excel-DNA
Hi,
I have this 256 characters limit issue for the below call
var evaluation = XlCall.Excel(XlCall.xlfEvaluate, input);
The "input" variable has the formula which is more than 256 characters in length.
The "input" variable has the formula which is more than 256 characters in length.
It is not allowed to break the formula.
Any advise?
Thanks
Reply all
Reply to author
Forward
0 new messages