Another Whitepaper - Developing UDFs for Excel 2007 and Excel Services

Off topic - AddressOf operator

John Greenan — Fri, 22 Sep 2006 19:39:26 GMT

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.

re: Another Whitepaper - Developing UDFs for Excel 2007 and Excel Services

Mike Rosenblum — Sat, 23 Sep 2006 18:58:37 GMT

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

re: Another Whitepaper - Developing UDFs for Excel 2007 and Excel Services

Mike Rosenblum — Sat, 23 Sep 2006 19:06:49 GMT

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

re: Another Whitepaper - Developing UDFs for Excel 2007 and Excel Services

Hein Rutjes — Mon, 25 Sep 2006 17:13:33 GMT

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

RTD thru UDFs

Danny Khen (MS) — Mon, 25 Sep 2006 19:19:16 GMT

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!

re: Another Whitepaper - Developing UDFs for Excel 2007 and Excel Services

David Gainer — Tue, 26 Sep 2006 08:12:05 GMT

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.

Wrapping RTD's

Hein Rutjes — Tue, 26 Sep 2006 10:45:36 GMT

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

re: Another Whitepaper - Developing UDFs for Excel 2007 and Excel Services

David Gainer — Thu, 28 Sep 2006 21:10:12 GMT

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.