Multiple UDF function calls -> One Webservice Call -> Refresh Cells

Oct 28, 2011 at 8:10 PM

Hello

I am after some advice on how to approach this.

I have a UDF method:

GetData(string arg1, string arg2, string arg3)

Now this can appear on many cells in worksheets with different arguments.  The UDF needs to call a Webservice to get the data it needs to display however calling the webservice for every cell is going to be very inefficient.

Now the Web Service can get multiple rows in one go by combining the arguments passed to it from Excel.  Is there a way that I can build up the request to send to the web service in the UDF calls then request the data in one go for all the cells from the web service, get the response then update the cells in Excel with the corresponding values that the Webservice returned?

Your help is much appreciated.

Kind Regards
Jon

Coordinator
Oct 28, 2011 at 8:19 PM
Edited Oct 28, 2011 at 8:20 PM

Hi Jon,

If you are using Excel 2007 / 2010 you can get some improvement by marking your functions as IsThreadSafe=true, and manually setting the number of calculation threads to be high - say 100.

But we really need some kind of asynchronous function support to do it properly. The best way to implement that for the moment is by creating an RTD server. I think this discussion came close: http://exceldna.codeplex.com/discussions/246314. And you can search for async and RTD on the Google group too.

I hope to make some improvements in async function support for the next version, probably with an RTD-based implementation as well as support for the Excel 2010 built-in async functions.

Of course caching the results already goes a long way, as could making more large-grained array functions.

-Govert  

Oct 31, 2011 at 8:50 AM

Hi Govert

I am using Excel 2010 (.NET 4.0) only so would you recommend the RTD Server solution or can I make use of the Excel 2010 built-in async functions that you mentioned?

Kind Regards
Jon 

Coordinator
Oct 31, 2011 at 8:58 AM

Hi Jon,

We haven't got support for the Excel 2010 asynchronous functions in Excel-DNA yet, so for now the RTD server would be your best plan.

I do hope to give some attention to the async support some time in the next few months, but you should probably not hold your breath :-)

Regards,

Govert