Welcome to MSDN Blogs Sign in | Join | Help

Microsoft Excel

The team blog for Microsoft Excel and Excel Services.
Another Whitepaper - Developing UDFs for Excel 2007 and Excel Services

Danny Khen is back (Danny wrote a number of posts on programmability topics over the past year) with a white paper on developing UDFs for Excel client and server.

In a few past posts, we discussed investments in user-defined functions (UDFs) in Excel 2007, and we showed (here, here and here) how UDFs can be used in an Excel solution that runs on both Excel 2007 and the new Excel Services. I now gathered all this information and elaborated some more, resulting in an article and an extensive set of code samples that we intend to publish on MSDN (I don’t have the precise date yet). I’d like to share a draft of this document and the code samples with you today. It essentially contains information about all the major paths you may take when developing an Excel client/server solution with UDFs: utilizing existing UDF code; having a core native function library; and basing your solution on managed functions. I hope you find this useful. I will appreciate any feedback of course – here in comments or through the email link.

Here is a link to the whitepaper.  And here is a link to the samples.

Just like with the draft XLL article that we posted on September 19th, please note that this one too is a draft, and the content may change for the final publication on MSDN.

Posted: Friday, September 22, 2006 5:36 AM by David Gainer
Filed under:

Comments

John Greenan said:

This is a bit cheeky, but what the hell, I am stuck and casting around for help anywhere.  I posted this on the excel.programming newsgroup and I reproduce it here wondering if anyone has any ideas....



In Excel 97 there was no inbuilt way to refer to the address of a function. Kaplan and Getz put together a workaround for this (
http://www.trigeminal.com/lang/1033/codes.asp?ItemID=19#19) which is very useful when required.

Excel 2000 and above have the native AddressOf operator, which offers very similar functionality with one major difference.  AddressOf  returns a long, but only takes as a parameter a function name written in text - no quotes, just the name.  It also will not accept a variable as a parameter.

Kaplan and Getz offered their workaround for Excel 97 with this method signature
Public Function AddrOf(strFuncName As String) As Long
In this case we can see that we can have a variable function name and the code figures out the address of the function and returns a long.  

So, the Kaplan & Getz code does not work in Excel 2002 and above (the platform I MUST use).  A few hacks around show that the issue is that the library they reference - vba332.dll is not implemeted in the same way in vbe6.dll.

So, with the constraints "MUST be in Excel 2002" and "must work with variable names for the callback address" I find myself stuck. I am working on a project where I cannot
re-engineer to use text for the function names, they have to be variables and I cannot use another version of Excel.


# September 22, 2006 12:39 PM

Mike Rosenblum said:

Hi John,

I tracked down your thread to here:

<a href="http://msdn.microsoft.com/newsgroups/default.aspx?&query=addressof&lang=en&cr=US&guid=&sloc=en-us&dg=microsoft.public.excel.programming&p=1&tid=bf92f52f-46d7-4435-a7f1-9423c7ebfcf2">AddressOf limitations 2002 vs. 97 in excel.programming</a>

I replied, but I don't know if I was helpful. My guess is that you're aware of the suggestions I made, but the flip-side is that it's hard to understand why you are saddled with the requirements that you state -- namely a string-based AddressOf operator.

Anyway, this is probably not the right place do discuss it, so I guess that anyone that is interested should just continue on your thread in excel.programming...

Mike
# September 23, 2006 11:58 AM

Mike Rosenblum said:

Sorry about the bad link in the above... I guess this system does not accept HTML links?

Hopefully this will work better:

http://msdn.microsoft.com/newsgroups/default.aspx?&query=addressof&lang=en&cr=US&guid=&sloc=en-us&dg=microsoft.public.excel.programming&p=1&tid=bf92f52f-46d7-4435-a7f1-9423c7ebfcf2

Mike
# September 23, 2006 12:06 PM

Hein Rutjes said:

Hi David,

We are busy investigating on how to put the new features in Excel 2007 to good use. We are very interested in the RTD functionality as an efficient way of getting our data into Excel. The RTD interface is however somewhat too generic for widespread use so we were thinking of making UDF functions in an XLL which wrap the RTD function. Will this be supported? And if so, would you recommend such an approach when used with very large quantities of these functions? Additionally would you recommend using multiple (in-process) RTD servers or a single (in-process) RTD server when it comes to performance?

Kind regards,
Hein
# September 25, 2006 10:13 AM

Danny Khen (MS) said:

Hein,
Not sure what you mean by "too generic"; however if you want to deploy the solution also to Excel Services (on Office SharePoint Server), RTD is not directly supported.
Shahar Prish has written about a way to implement something like what you're suggesting - RTD thru UDFs on the server. This can be found in 2 posts: http://blogs.msdn.com/cumgranosalis/archive/2006/05/23/RTDPart1.aspx and http://blogs.msdn.com/cumgranosalis/archive/2006/05/24/RTDPart2.aspx.
Please drop me a line at Danny.Khen AT microsoft DOT com if you'd like to keep discussing this.
Thanks!
# September 25, 2006 12:19 PM

David Gainer said:

Hein,

You can call =RTD() from an XLL.  It's less performant than calling from a worksheet directly, but should be faster than doing the UDF wrapper in VBA.

I don't know for sure, but we expect a single server with multiple topics would be more performant than multiple servers.  Also, having the server in-proc would most likely be more performant.
# September 26, 2006 1:12 AM

Hein Rutjes said:

Thanks Danny & David,

I was wondering about one more thing. What is the performance penalty of wrapping the =RTD() function? Will there only be a performance penalty when the input-arguments to the function change? After the RTD registrations are in-place the wrapping function will no longer be called right (unless the arguments change of course)?

Kind regards,
Hein
# September 26, 2006 3:45 AM

David Gainer said:

Hi Hein,

I am pretty sure that the penalty would be every time the data changed.  When we do RTD work, we mark the currently calcing cell as "having RTD data", so each time an RTD update is received, we'll calc the cell.
# September 28, 2006 2:10 PM
New Comments to this post are disabled
Page view tracker