3

and plots the result in 2-D cells in Excel? I am using C#, .NET, Excel-DNA, Add-In Express RTD,

Thanks

Edit I tried http://excel-dna.net/2011/01/30/resizing-excel-udf-result-arrays/, does not work for me. I want sth like this MyUDF(param1, param2) returns a 2-D array like a table based on lengths of param1, param2 say param1 is "a,b,c", param2 is "high,low", expected to return an array [3,2], and display the result in 3 rows by 2 columns

I also tried Excel ExcelDNA C# / Try to copy Bloomberg BDH() behavior (writing Array after a web request), it works fine. but my requirement is backgroundworker will call RTD function, the RTD function is a multi-step process, first it will login(asychronous, then subscribe(asychronous), when I call xlcall.RTD from backgroundworker, it gives accessinviolation exception

Community
  • 1
  • 1
toosensitive
  • 2,335
  • 7
  • 46
  • 88
  • What have you tried, and what problems did you run into? What are "2-D cells"? http://blogs.msdn.com/b/gabhan_berry/archive/2008/04/07/writing-custom-excel-worksheet-functions-in-c_2d00_sharp.aspx – Tim Williams Sep 05 '12 at 22:01
  • I have tried http://excel-dna.net/2011/01/30/resizing-excel-udf-result-arrays/, it returns #Value!, not working for me. see more in edit – toosensitive Sep 05 '12 at 22:22

1 Answers1

1

Excel UDF and RTD are passive things. When the user enters a formula, Excel invokes the UDF (RTD function) to calculate the formula and to put the result back to the calling cell(s). Unlike RTD, a UDF can find out where it is called from, but both of them cannot “extend” the calling range. Extending the range would also modify the formulas/values in other cells – and this is not among the things allowed for the UDF; the RTD server doesn't have access to the Excel object model at all.

Consider combining a COM add-in and RTD server in the same assembly. The COM add-in can handle the SheetChange event to determine if the user enters a formula that refers to your RTD. When this occurs, the COM add-in can fill any required range of cells with formulas or data and also set some flag(s) or store data accessible by your RTD. Also, the RTD can call a public method defined in the add-in and it can fill any cells this way. If you choose this option, you undoubtedly will call such a method in the RefreshData event. Note however that at this moment Excel may not be prepared to COM calls that modify the cells. To avoid potential problems, I recommend modifying the cells after a delay, see ADXAddinModule.SendMessage() method and ADXAddinModule.OnSendMessage event. The point is that the OnSendMessage event occurs only when Excel finishes its tasks and when it is ready for requests.

What do you think?

Regards from Belarus (GMT+3),

Andrei Smolin Add-in Express Team Leader

Andrei Smolin - Add-in Express
  • 444
  • 2
  • 8
  • thank you, Sir. Do you have any example? How do I make sure OnSendMessage event occurs after Excel is ready? – toosensitive Sep 11 '12 at 14:46
  • Thanks, Sir. I am thinking to call a public method in RefreshEvent. I will remember callers of each function in a dictionary, in refreshevent, I will look up its caller and then plot in excel. I did not get sheetChange event part. thanks – toosensitive Sep 12 '12 at 14:33
  • See HowTo: Create a COM add-in, XLL UDF and RTD server in one assembly - http://www.add-in-express.com/creating-addins-blog/2010/03/24/addin-xll-rtd-one-assembly/. – Andrei Smolin - Add-in Express Sep 12 '12 at 15:19
  • The SendMessage event occurs only when Excel is ready; you don't need to do anything else. SheetChange occurs only when you enter something; it doesn't occur when your RTD refreshes the cell(s). I suggest that you write us at the support desk (find it readme.txt). – Andrei Smolin - Add-in Express Sep 12 '12 at 15:21
  • I see this http://www.add-in-express.com/forum/read.php?FID=5&TID=4947, it says "You can safely update cells from XLL macro functions only." so I wonder how to do that under addin express context. thanks – toosensitive Sep 13 '12 at 17:03