Excel 2007 investments in UDFs #1

re: Excel 2007 investments in UDFs #1

Andrew Walaszek — Wed, 03 May 2006 19:31:49 GMT

Does an Excel Undo mechanism exist for the implementation of managed UDFs? This also applies for any operations performed by a third-party Add-In.

Regards,
Andrew

re: Excel 2007 investments in UDFs #1

Harlan Grove — Thu, 04 May 2006 01:23:05 GMT

A question I didn't ask in the original blog post on XLLs and UDFs: did you increase the limit on string argument and string return value length to/from XLL UDFs? One of the most frequent hassles with XLL UDFs for string processing is the 255 char limit.

re: Excel 2007 investments in UDFs #1

Harlan Grove — Thu, 04 May 2006 01:58:58 GMT

With regard to the wonderful sample udfs, these have been dealt with ad nauseum in the Excel newsgroups.

WehWordM: return n_th word in s delimited by d
=MID(s,FIND(CHAR(127),SUBSTITUTE(d&n,
d,CHAR(127),4)),FIND(CHAR(127),
SUBSTITUTE(s&d,d,CHAR(127),4))
-FIND(CHAR(127),SUBSTITUTE(d&s,
d,CHAR(127),4)))

WehWordCountM: return # words in s delimited by d
=1+(LEN(s)-LEN(SUBSTITUTE(s,d,"")))/LEN(d)

Not good examples of "functions that we wish Excel had."

Any chance you could show examples of udfs that *CAN'T* be performed by built-in functions alone (nontrivial udfs)? Maybe something that only needs the arguments, like a generalized string concatenation udf that would concatenate all items in all its arguments into a single string? And don't let noncommutivity stop you: Excel's NPV function seems to be able to iterate through ranges and arrays in a specified order, so generalized concatenation should iterate the same way.

re: Excel 2007 investments in UDFs #1

a user — Thu, 04 May 2006 03:34:56 GMT

I think I understand the reasons for the UDF framework, and for deprecating VBA. But I do not understand why it is called "User Defined Function" when this is clearly, expressly, and exclusively for Developers.

What happens to the customer base when Users are deprecated?

re: Excel 2007 investments in UDFs #1

Colin Banfield — Thu, 04 May 2006 05:00:41 GMT

<<WehWordM: return n_th word in s delimited by d
=MID(s,FIND(CHAR(127),SUBSTITUTE(d&n,
d,CHAR(127),4)),FIND(CHAR(127),
SUBSTITUTE(s&d,d,CHAR(127),4))
-FIND(CHAR(127),SUBSTITUTE(d&s,
d,CHAR(127),4))) <<

Harlan, why would the average user (or even a seasoned user for that matter) prefer to use a formula with thirteen functions (assuming that he/she can figure it out in the first place) over a single function with two arguments? You have to remember, perhaps 90% of all Excel users are not as smart or as masochistic as Harlan Grove.

re: Excel 2007 investments in UDFs #1

Harlan Grove — Thu, 04 May 2006 20:02:08 GMT

Colin,

How many current Excel users could recognize the udf code as written in C#? Who is the audience for this particular blog posting?

You could write a udf for everything that requires 3 or more built-in function calls. Would that make sense? How many more functions should Excel have? What would be the most useful additional functions?

Alternatively, which is easier: remembering how to write longish formulas or remembering a +1000 functions?

My point was that there are things that can *ALREADY* be done using built-in functions alone. It'd be better to show examples of new udfs that do things that *CAN'T* be done by udfs alone. A generalized concatenation udf would have shown whether the new XLLs could accept variable numbers of arguments, detect whether any of those arguments were arrays, and if so show how to iterate over them. A udf wrapper around the split method of the string class isn't interesting or illuminating, not to mention there have been newsgroup postings showing VBA udf wrappers around VBA's split function which return the array of tokens.

Also, it'd be a good thing to design udfs to match up with Excel's capabilities. When it comes to functions, that means supporting use in array formulas. WehWordM and WehWordCountM should accept arrays of strings as 1st arg or arrays of positions as 2nd arg and return array results. A modest modification to my long formula can do that. The udfs linked to by the blog posting can't. Again, for the intended audience for this blog posting, wouldn't it have been useful to show how (whether) new XLL udfs return array results? If those udfs can only return scalars, claiming it provides V1 features is an overstatement.

re: Excel 2007 investments in UDFs #1

Danny Khen — Thu, 04 May 2006 23:55:55 GMT

Thanks everyone for the feedback and questions! Some answers (in order of appearance):

Andrew: I was discussing server-side UDFs, and we don't actually have editing capabilities on the server, so I am not sure what kind of Undo mechanism you are talking about. Please drop me an email thru the "Email" link at the top and we can discuss.

Harlan: Your comments and the discussion that followed are interesting, however I was not trying to demonstrate the implementation of any particular feature. The point here is to show how to create managed UDFs for Excel Services. UDFs as their name implies are all about your own extensibility needs, whatever they may be; we're just providing the framework and defining the interface.

The server UDFs can indeed return an array and be called in an array formula. You'd need to define your return value as object[] or System.Double[,] etc. We will have full reference documentation as part of the Office SharePoint Server 2007 SDK, which is due to be published with Office Beta2 as far as I know.

A user: "User-Defined Functions" are just an industry term. They refer to functions defined by developer users, as opposed to end-users. We did not deprecate VBA in Excel; however it is not supported on Excel Services, since we believe that managed extensibility is the way to go for a server product (and we will be extending what we support in this area in future releases).

We're going to have a follow-up post on this blog to show how UDFs in existing XLL addins can be used on the server thru managed wrappers - stay tuned.

re: Excel 2007 investments in UDFs #1

Danny Khen — Fri, 05 May 2006 00:04:35 GMT

Harlan, I missed your first question about the limit on string argument and string return value length.
Short answer - we did increase the limit. A bit longer answer: We added Unicode string types, and for those, you can pass strings up to 65535 characters long. Of course, depending on the purpose of the string, the actual limit may be smaller; e.g. if you are setting a string into a cell, the Excel 2007 limit on cell string length is 32767 chars, so anything longer will be truncated.

re: Excel 2007 investments in UDFs #1

Colin Banfield — Sat, 06 May 2006 01:38:54 GMT

Hi Harlan,

<<How many current Excel users could recognize the udf code as written in C#? Who is the audience for this particular blog posting?<<

Oh, I was thinking of the convenience of the UDF to the end user, who woundn't know or care what code the UDF is written in.

<<You could write a udf for everything that requires 3 or more built-in function calls. Would that make sense? How many more functions should Excel have? What would be the most useful additional functions?<<

There are few if any ATP functions (for example) that can't be emulated with basic math functions. The ATP functions are popular among users nonetheless.

<<My point was that there are things that can *ALREADY* be done using built-in functions alone. It'd be better to show examples of new udfs that do things that *CAN'T* be done by udfs alone. A generalized concatenation udf would have shown whether the new XLLs could accept variable numbers of arguments, detect whether any of those arguments were arrays, and if so show how to iterate over them. A udf wrapper around the split method of the string class isn't interesting or illuminating, not to mention there have been newsgroup postings showing VBA udf wrappers around VBA's split function which return the array of tokens.<<

No argument here, when viewed from the developer's perspective.

<<Also, it'd be a good thing to design udfs to match up with Excel's capabilities. When it comes to functions, that means supporting use in array formulas. WehWordM and WehWordCountM should accept arrays of strings as 1st arg or arrays of positions as 2nd arg and return array results. A modest modification to my long formula can do that. The udfs linked to by the blog posting can't. Again, for the intended audience for this blog posting, wouldn't it have been useful to show how (whether) new XLL udfs return array results? If those udfs can only return scalars, claiming it provides V1 features is an overstatement. <<

Well, I try to avoid array formulas wherever possible but I have to admit, there are times where they're darned convemient to use. Personally, I'd like to examples of XLL UDFs that support 3D ranges.

re: Excel 2007 investments in UDFs #1

Danny Khen — Sat, 06 May 2006 02:50:38 GMT

Ranges / array formulas:
In his blog, Shahar had a post with sample code for UDFs that receive and return both 1- and 2- D arrays. These can be used in array formulas.
Here's the link:
http://blogs.msdn.com/cumgranosalis/archive/2006/04/11/WebServiceAsyncUdfs.aspx

re: Excel 2007 investments in UDFs #1

Harlan Grove — Mon, 08 May 2006 20:30:36 GMT

Colin,

I'd also like udf examples that use 3D references, but that'd require a fundamental, nontrivial addition to Excel's object model since there are no such objects available through XL11.

re: Excel 2007 investments in UDFs #1

Harlan Grove — Mon, 08 May 2006 21:54:53 GMT

Colin,

Goofed. I was thinking in terms of VBA udfs, and there are no 3D references there. Since it appears that udf support is moving away from VBA, there may never be VBA support for 3D references. But XLL udfs through XL11 accept 3D references, Longre's MOREFUNC.XLL's THREED udf being the principal example I know of.

Making Excel Services UDFs Work in Excel 2007

Microsoft Excel 2007 (nee Excel 12) — Fri, 01 Sep 2006 20:32:55 GMT

Shahar Prish, one of the developers on the Excel Services team, has recently posted a few entries on...

Excel su Sharepoint Server come client server

Tecnologie .NET (Dotnet) — Fri, 22 Sep 2006 15:55:16 GMT

Excel Sharepoint UDF server side

Excel su Sharepoint Server 2007 come client server

Tecnologie .NET (Dotnet) — Fri, 03 Nov 2006 09:25:04 GMT

Excel Sharepoint UDF server side

Excel su Sharepoint Server 2007 come client server

Tecnologie .NET (Dotnet) — Fri, 10 Nov 2006 10:51:29 GMT

Excel-Sharepoint-Services-UDF Serverside Excel

Christian Stich on Excel Services: Financial option valuation implemented with Monte Carlo simulation using multithreaded User Defined Functions (UDFs)

Microsoft Excel — Wed, 15 Aug 2007 06:25:47 GMT

Today’s author: Christian Stich, a program manager on the Excel Services team who likes to combine his

Christian Stich on Excel Services: Financial option valuation implemented with Monte Carlo simulation using multithreaded User Defined Functions (UDFs)

Noticias externas — Wed, 15 Aug 2007 06:37:41 GMT

Today’s author: Christian Stich, a program manager on the Excel Services team who likes to combine his

Building an Asset Tracking Application in Excel Services – Part 3b of 5

Microsoft Excel — Wed, 02 Jul 2008 20:01:52 GMT

Today's author, Dan Parish, continues his discussion on building an Excel Services solution. In Part

Building an Asset Tracking Application in Excel Services ??? Part 3b of 5, Microsoft Office developer blog

Mon, 14 Jul 2008 22:28:01 GMT

PingBack from http://ms-office.yourbloggingpro.com/2008/07/building-an-asset-tracking-application-in-excel-services-%e2%80%93-part-3b-of-5/

iterate formula in excel

iterate formula in excel — Wed, 16 Jul 2008 16:14:55 GMT

PingBack from http://devyn.onlinevidsguide.info/iterateformulainexcel.html

Building an Asset Tracking Application in Excel Services ?????????????? Part 3b of 5, Microsoft Office developer blog

Sat, 19 Jul 2008 00:10:24 GMT

PingBack from http://ms-office.yourbloggingpro.com/2008/07/building-an-asset-tracking-application-in-excel-services-a%c2%80%c2%93-part-3b-of-5.html

UDFs in Microsoft Excel 2007 services « Knowledgebase

UDFs in Microsoft Excel 2007 services « Knowledgebase — Tue, 31 Mar 2009 09:51:17 GMT

PingBack from http://roelvanlisdonk.wordpress.com/2009/03/31/udfs-in-microsoft-excel-2007-services/

Microsoft Excel Excel 2007 investments in UDFs 1 | debt consolidator

Microsoft Excel Excel 2007 investments in UDFs 1 | debt consolidator — Mon, 15 Jun 2009 22:57:00 GMT

PingBack from http://mydebtconsolidator.info/story.php?id=13358