Developing User-Defined Functions for Excel 2007 and Excel Services

Summary: Learn how to develop and reuse user-defined functions (UDFs) for use in Microsoft Office Excel 2007 and Excel Services in Microsoft Office SharePoint Server 2007. (13 printed pages)

Danny Khen, Microsoft Corporation

Shahar Prish, Microsoft Corporation

February 2007

Applies to: Microsoft Office Excel 2007, Microsoft Office SharePoint Server 2007

Download OfficeExcelUDFSamples.exe.

Contents

• Overview of Developing User-Defined Functions for Excel 2007 and Excel Services

• Understanding the Building Blocks

• Reusing UDFs in Existing XLL Add-ins

• Using Native Code Function Libraries

• Developing Managed-Code User-Defined Functions

• Additional Considerations

• Alternative Approaches Using Automation Add-ins

• Deploying an XLL with the Managed-Code UDFs It Wraps

• Conclusion

• Additional Resources

Overview of Developing User-Defined Functions for Excel 2007 and Excel Services

You can develop a package of Excel user-defined functions (UDFs) that you can use across Microsoft Office Excel 2007 and Excel Services in Microsoft Office SharePoint Server 2007. This article provides guidance that helps you address both existing Excel UDFs and new UDF code, and that describes how to create UDF solutions that work across both client and server. Learn to map your particular solution needs to one of the approaches discussed here. The approach you choose should take into consideration both your existing UDF code base and your new design goals.

This article discusses UDF solutions that address the following issues:

• Reuse of UDFs in existing XLL add-ins. Intended for a developer who wants to use an existing set of UDFs—implemented as Excel XLL add-ins—in server-based solutions as well.

• Use of a native code function library. Intended for scenarios where a "core" function library exists, for example, if you have a set of Microsoft Visual C++ functions on which you want to base both client and server UDFs. You can also use this option for when you have existing XLLs that cannot easily be used directly. For more information, see Reusing UDFs in Existing XLL Add-ins in this article.

• Use of managed-code UDFs. Intended for developers writing UDFs in managed code that are used on both client and server computers.

This article also discusses deployment, security, performance, and robustness of UDFs, as well as alternative approaches to using UDFs.

Scenarios

You might have a large amount of development resources invested in Excel UDFs, for example, in custom calculation packages or in custom data feeds.

Office SharePoint Server 2007 provides a new capability to deploy Excel workbooks on the server through Excel Services. This capability enables customers to re-use existing UDF-based Excel solutions on Office SharePoint Server 2007.

Following are the likely two main scenarios in which you might require your UDFs to work across client and server environments:

• You need the Open in Excel command to be available from Excel Services.

  In this scenario, your user typically performs the following actions: Browse to a workbook on Office SharePoint Server 2007, and then, at some point, clicks Open in Excel.

  You want UDFs that are called from the workbook to continue to perform as expected on your client computer.

• You are designing a workbook for use on Office SharePoint Server 2007.

  In this scenario, you typically perform the following actions: Use Excel to design and create a server-side workbook, and save the workbook to Office SharePoint Server 2007.

  The UDFs that are called from the workbook on the Office SharePoint Server 2007 computer continue to work as expected.

Implementing UDFs that work across both client and server computers is a challenge because the Excel client application and Excel Services are based on different technologies:

• Excel, a client-side native code application, supports a host of native code add-in technologies, such as Microsoft Visual Basic for Applications (VBA), COM, Automation, and XLL. The focus on performance and design flexibility in Excel led to an investment in XLLs during the development of Excel 2007.

• Excel Services, which is part of Office SharePoint Server 2007, is implemented primarily in managed code. As a server feature and as a platform, Excel Services focuses on providing robust server performance. You can extend Excel Services through its support of managed-code UDF assemblies.

Understanding the Building Blocks

Before you determine which approach to take with UDFs, let us examine the components of Excel 2007 XLL add-ins and Excel Services managed-code UDFs. Understanding the role of these components in the client and the server technologies can assist you in determining your approach.

Excel 2007 XLL Add-ins

An XLL is a particular type of DLL that is written so that Excel can open it directly. One of the most common uses of XLLs is to create UDFs to supplement the intrinsic functions in Excel. Developers who write XLLs do so mainly because of the performance they provide. When Excel registers an , it adds it to its internal list of functions and calls it just as it would any of the internal functions—without having to process it through any intermediate layer, such as a COM layer.

Excel 2007 has made three important changes to the XLL interface to give developers access to new functionality. With Excel 2007, XLL authors now have support for the following:

• The "Big Grid," which in Excel 2007 increases the maximum number of rows per worksheet from 65,536 to over 1 million, and the number of columns from 256 (IV) to 16,384 (XFD).

• More function arguments

• Multithreaded calculation

Other changes in Excel enable developers to take advantage of functionality that already existed in earlier versions of Excel, but which was not exposed to XLLs, such as wide character (Unicode) strings.

Finally, changes to the XLL interface now lift various limitations, and XLLs can use any arguments that Excel 2007 can, for example, longer strings and more function arguments.

XLLs are the add-in technology of choice for Excel. This article focuses on XLLs as the primary client UDF technology for writing client and server UDF solutions.

Excel Services Managed-Code User-Defined Functions

Office SharePoint Server 2007 introduces Excel Services and its extensibility mechanism for writing UDFs. Excel Services is a feature in Office SharePoint Server 2007 that enables calculation, display, and exploration of the Excel workbooks that are located on the server. Excel Services also offers an extensibility mechanism for writing UDFs, much as you can extend Excel by writing UDF add-ins.

The following sections offer important design issues that you should consider for writing UDFs to extend Excel Services.

Excel Services UDFs are Written in Managed Code

Server-side UDFs are implemented as methods in Microsoft .NET Framework 2.0 assemblies. Excel Services directly supports only managed-code UDFs because of the performance, stability on the server, and security they can provide. You can use existing native function libraries and Excel UDFs with Excel Services, however, by "wrapping" them using the new managed-code server UDF style. For more information, see Managed-Code UDF Wrapper for Excel Services in this article.

Excel Services UDFs Have Restrictions

Excel Services, andExcel Services UDFs have the following restrictions:

• **No support for the Excel object model.   **Excel Services does not support an object model such as that for Excel. The entire interface for UDF with the Excel worksheet is performed through the signature in a method call. The Excel formula passes arguments into the function, and return values are passed back into the formula.

• Restrictive data types.   Excel Services data types are more restrictive than Excel add-ins in their support of data types and type conversion.

  Note

  For more information about how to handle data types with Excel Services UDFs, see Data-Type Handling with Excel Services User-Defined Functions.

• Error handling returns #VALUE errors.   All exceptions thrown by the UDF code are returned into the Excel worksheet as #VALUE errors.

• Simple load/run-time behavior.   The load/run-time behavior of all UDFs is not complex; they are currently run together with Excel Services code in the same application domain.

• Requirement for all UDFs to be thread-safe.   Because Excel Services is a server feature, and its calculation engine runs as a multithreaded back-end service, to support multiple user sessions, Excel Services requires (and assumes) that all UDFs are thread-safe.

  Note

  By ensuring that your XLL UDFs are thread-safe, you can help ensure multithreaded calculation of the UDFs when they are called from Excel 2007. In addition, you help ensure that they can run on the server in the context of many concurrent user sessions.

• **Security of Excel Services UDFs must be controlled by the administrator.   **A server administrator can control UDF security as follows:

  • Register assemblies on a server list of trusted UDF assemblies in Excel Services. Excel Services loads and runs only those methods for an assembly that an administrator has registered on the list of trusted UDF assemblies.

  • Use code access security infrastructure in .NET Framework to restrict UDF access to resources. For example, if a UDF package is simply a collection of mathematic calculation functions, an administrator can turn off the UDF assembly ability to access Web services, external data, and native code to help make the server environment safer for users.

Reusing UDFs in Existing XLL Add-ins

If you have an existing set of UDFs that are currently implemented as XLL add-ins, you might want to continue to use them on the client computer, but also take advantage of them on the server. The following scenarios describe how to do this and describe the constraints that are involved.

For those XLLs that meet the criteria for reuse, you can develop a managed-code UDF wrapper, with a new managed-code method to call each of the XLL UDFs. The method handles the interoperability of the managed code and native code, and maps the C API arguments and return values to .NET Framework types. You can also use C++/CLI to implement the wrapper, as described in Existing XLL Add-in in this article.

Existing XLL Add-in

From a developer's perspective, Excel Services UDFs support an interface that is quite different from that of XLLs. However, from the Excel worksheet designer's perspective, the server and client UDFs must have an identical interface. This creates certain constraints on XLL UDFs, which can be wrapped and used with Excel Services. Following are the constraints:

• **No callbacks.   **The C API in Excel that allows an XLL to call back into Excel (to run Excel functions or to exchange data with the Excel file) is not supported on Excel Services.

• No user interface.   The user interface extensions in Excel are not supported on Excel Services.

• Required thread-safety.   Excel Services UDFs must be thread-safe, therefore, the XLLs that they call must also be thread-safe.

• No user-specific information as global state.   XLLs might assume that the global state is in the scope of a single Excel session, but on the server it extends the single user session to all server-side workbook sessions on the computer. This is quite different from the client, and as a result, is incompatible with the client behavior.

• No argument type conversion.   Excel is not strict in how it converts arguments and return value types. But because Excel Services UDFs are based on the .NET Framework, they are much stricter. Data-Type Handling with Excel Services User-Defined Functions describes which types are supported, and how Excel Services UDFs behave for those types in conjunction with various Excel types.

  • Specifically, passing cell error values to an Excel Services UDF is not supported.

  • In many cases, if you have an XLL that depends on type conversion, you can still use it with Excel Services and achieve very similar results. However, you would need to know or assume certain behavior about the way your UDF is used in Excel workbooks.

    For example, assume you have an that accepts a string argument, and assume that this UDF typically gets called from numeric cells in addition to cells with string values. You can write an Excel Services UDF wrapper that accepts an object[] argument. The wrapper checks the actual run-time value of the object, and ensures that it is either a string or a number (and throws an exception for other cases). It then passes a string as-is to the XLL, or convert the number to a string and pass it to the XLL.

Some XLLs might meet these requirements; others might be close enough to be worth the investment to make the necessary modifications. In those cases, you can consider using this wrapper approach.

If the design of your XLL varies too far from these constraints, you can consider an alternative approach, described in Using Native Code Function Libraries.

Managed-Code UDF Wrapper for Excel Services

A managed-code UDF wrapper serves as the interface for Excel Services to your XLL UDFs. The classes and methods adhere to the optimum way to design server UDFs, while the calculation logic remains in the XLL. The wrapper handles all the necessary operations plumbing in between.

The main design goal for this wrapper is to expose an identical interface from the worksheet designer's perspective, such that calling each UDF from the Excel worksheet supports the same cases (and yields the same results) on the server as it does on the client. This enables you to deploy your client workbooks on the server, and have the workbooks appear as if they are authored specifically for that new environment.

The C++/CLI language is particularly suited for creating this kind of native/managed-code bridge. Therefore, we use it to demonstrate writing such a wrapper.

The same sample solution discussed previously, WishExcelHad-XLL, has another project called WehServerX, that shows a C++/CLI managed-code wrapper for the two XLL UDFs. Each wrapper method does the following:

1. Performs string conversion:

   1. Acquires native pointers to the managed-code argument strings.

   2. Converts the argument strings from Unicode to ASCII strings.

   3. Converts each string from C-style to byte-count (Pascal-style).

2. Calls the .

3. Maps the XLL error mechanism into .NET Framework exceptions.

4. Performs the reverse of the steps in step 1 in case the XLL return value is a string:

   1. Converts each string from C-style to byte-count (Pascal-style).

   2. Converts the argument strings from Unicode to ASCII strings.

   3. Acquires native pointers to the managed-code argument strings.

In some cases, you can combine the XLL and the managed-code wrapper into the same DLL, which serves a dual purpose: It is loaded as a native DLL in Excel, and it is loaded by the common language runtime on the server. This is possible if both the XLL and the wrapper are written in C++. Other cases result in an XLL binary (or maybe DLL binary) and a separate assembly.

Deployment to the Server

You should be aware of the following server deployment behaviors:

• If there are separate DLLs (the XLL and assembly), they are installed to the same folder.

• Your XLL probably contains C API event-handler functions, which make calls to C API functions, such as Excel4(). However, because these functions never get called from your new managed-code wrapper or from Excel Services, you do not need to make the Excel client run time available on the server, which means you do not need to install any Microsoft Office client product.

The following additional notes apply to any managed-code UDF assembly deployed to Excel Services—not just to the managed-code wrapper:

• An administrator must add the assembly to the trusted UDF assemblies list, so that Excel Services can trust and load it.

• Server-side Excel workbooks must reside in trusted file locations, that is, document libraries or network file shares that are listed with Excel Services as trusted locations for opening Excel files. An administrator must turn on and enable UDFs option for each trusted file location that contains workbooks that need to call UDFs. For security purposes, this option is turned off by default for a new trusted file location.

• An administrator can use the .NET Framework code access security tool to control code access security for the UDF assembly. The UDF assembly must be granted permission to call native code, because this assembly is only a wrapper to a native XLL. Any other code access security settings can be set and modified. However, remember the settings control only what the managed-code portion of your solution can do. The .NET common language runtime has no control over the native XLL UDF after it begins to execute.

Using Native Code Function Libraries

There are many kinds of native code function libraries for calculations. Sometimes these libraries are used across solutions and platforms. For example, a math library can be used from Excel UDFs, but also from a server solution running on an operating system other than one based on Microsoft Windows or Windows Vista. C and C++, as standard languages with cross-platform implementations, have made creating and using such function libraries easy to do.

This section shows how to use such a function library as the basis for Excel UDFs that can run on both client and server. The idea is simple: Create two types of wrappers, one for the Excel client and one for Excel Services. The two wrappers expose an identical interface to the Excel worksheet designer, so calling each UDF from the Excel worksheet looks and feels the same in both cases, making it possible to use the same workbooks on the client and server.

About Core Native Code Function Libraries

The requirements for a function library, if it is to be usable in a client/server UDF solution, are quite similar to the requirements for existing XLLs. General-purpose calculation libraries should typically be compliant already, because they are written as pure calculation algorithms, without Excel-specific user interface, event handling, data types, callbacks, and so on.

The function library can be a DLL or a static library. Here, the concept is demonstrated using a DLL.

The code sample download includes the sample solution in a folder called WishExcelHad-core. Open the solution in Visual Studio 2005. In this solution is the class WehText in the WishExcelHad.cpp file, which demonstrates a C++ core calculation library, with the two methods Word and Wordcount.

Create an XLL Wrapper Add-in for Excel

An XLL wrapper add-in for Excel has the structure of any standard XLL with UDFs. The main difference is that the calculation logic is implemented elsewhere. Each UDF in the XLL must map to C API arguments in Excel and return values to simple C/C++ function calls. This is demonstrated by the WehXLL project in the sample solution.

Create a Managed-Code UDF Wrapper for Excel Services

This wrapper is essentially the same as the managed-code wrapper described previously in Reusing UDFs in Existing XLL Add-ins, but it is simpler. It does not need to handle the intricacies of mapping to the C API in Excel because it wraps a simple function library. It still must handle managed code to native code interoperability, and expose Excel Services-attributed UDF methods. This is demonstrated by the WehServer project in the sample solution.

Developing Managed-Code User-Defined Functions

If you want to develop your UDF library in managed code (for reasons, see Additional Considerations), or you already have a managed-code function library, using a managed-code UDF might be appropriate for you.

In this approach, you develop a core function assembly, and then wrap it with an XLL add-in for the Excel client, and a UDF assembly for Excel Services.

Because the server-side run time is not available on the client, you want to keep the client side of your solution independent of the server's run time. As a result, you keep the UDF assembly separate, as a wrapper, so that the UDF assembly refers to an Excel Services run-time assembly, which is found on the server.

If deploying the Excel Services run-time UDF assembly on client computers is possible, together with your UDF and the XLL wrapper, then you can simplify this approach by adding the server UDF attributes to your core managed-code function library, and avoid the need for a server wrapper.

About Core Managed-Code Function Libraries

A core managed-code function library is a "pure" calculation library, free of any Excel client or Excel Services APIs and considerations.

The constraints are similar to those of the core native code function library you saw in the Using Native Code Function Libraries approach, although there are differences between native code and the .NET common language runtime.

In the code sample, the core managed-code library is combined with the server UDF assembly, thus, avoiding a managed-code wrapper. Adding the server attributes is all there is to it—there is nothing else that is Excel Services-specific in this code.

Create an XLL Wrapper Add-in for Excel

An XLL wrapper add-in for Excel is similar to the one described in the Using Native Code Function Libraries approach, but it also must take care of interoperability to an assembly.

In the sample solution, the project WehXLL shows a C++/CLI XLL, in which the two UDF methods call the equivalent methods in the managed-code WehServerM class.

You should consider the following issues when using this XLL wrapper:

• The C++ file with the UDFs (in this case, WehUDFs.cpp) must have the common language runtime support enabled. This is the /clr compiler option. Or, in the Visual Studio property page for the CPP file, under C/C++ configuration properties, select General. Under Compile with Common Language Runtime support, select Common Language Runtime support (/clr).

• This XLL must load the assembly with the actual managed-code functions. Deploy the XLL and the managed-code assembly to the same folder, and make sure that the common language runtime finds and loads the assembly. ‎Deploying an XLL with the Managed-Code UDFs It Wraps shows how to make this happen.

Create a Managed-Code UDF Wrapper for Excel Services

A managed-code UDF wrapper for Excel Services is the simplest wrapper to create. For each managed-code function in the core library, the wrapper exposes a UDF that has an identical interface, and a single statement—a call to the core function. The only added value here is the Excel Services attributes, which make these functions recognizable to the server as UDFs.

As mentioned earlier, we are not demonstrating a separate wrapper in the code sample download but are combining it with the core managed-code library.

Additional Considerations

This section presents issues for workbooks, performance, security, and robustness that you should consider before choosing an approach for using UDFs.

Workbook Issues

Some limitations apply to Excel workbooks, which use UDFs and are deployed on both client and server. The main limitation, however, concerns data type conversion.

Excel Services does much less type conversion than Excel does with native code UDFs. For example, if a UDF defines a double argument, the worksheet should be calling it with a number—not a string. Passing a string succeeds on the client, it fails on the server.

‎For more information about how Excel Services handles types in the interface with UDFs, see Data-Type Handling with Excel Services User-Defined Functions.

Performance Issues

The approaches discussed in Reusing UDFs in Existing XLL Add-ins and Using Native Code Function Libraries are optimal on the client. The code incurs no interoperability costs whenever a UDF is called.

In addition, XLLs specifically perform better than other add-in types. They are called directly by Excel (no intermediate interface layer is used), and in Excel 2007 they benefit from multithreaded recalculation (UDFs in other add-in types do not take part in multithreaded recalculation).

On the server, however, these approaches are less than optimal. The server makes a call to the managed-code UDF wrapper, and then an interop transition outside of the common language runtime occurs for the wrapper to call the native code UDF. If your solution has few UDF calls and spends a long time in a complex algorithm within the UDFs, this may not be an issue. The overhead becomes less significant. But when many short-lived calls are made, and in particular when large arrays are passed into and out of the UDF, there can be a significant performance hit.

The Developing Managed-Code User-Defined Functions approach is best for server solutions. No interop hops occur when a UDF is called. And on the client, when wrapped with an XLL UDF, the managed-code UDF provides the best performance possible for a managed-code UDF in Excel.

Security Issues

The .NET Framework provides the capability of code access security. With code access security, both the code developer and the administrator who deploy the UDFs can control the type of resources to which the code has access, to a more detailed level. For example, you can grant or prevent code access to external data or control the ability to call Web services.

From a server security perspective, the Developing Managed-Code User-Defined Functions approach is by far the best. When a native code UDF runs on the server, after the common language runtime calls it out into the native "wild" that is outside of the common language runtime, it can no longer control the UDF access to resources.

Achieving Server Robustness

Your server UDFs run within a server process, shared by many users. A severe bug in a native code UDF can cause the server process to fail, affecting the session states of many users at the same time. By using managed code, you can catch and handle most exceptions, making it less likely that a UDF will cause the server to fail.

Therefore, the Developing Managed-Code User-Defined Functions approach is superior from a server robustness perspective as well.

Choosing an Approach

Which approach you should use depends on your situation. If you have a big investment in native code, or if your UDF-based solution is likely to be used frequently on the client, you might favor native code over server considerations.

However, when your solution is designed to run mainly on the server, and the Excel client serves primarily as the authoring environment for server-side workbooks, you might want to seriously consider investing in writing your UDFs as managed code, and using a native wrapper to call them on the client—the Developing Managed-Code User-Defined Functions approach.

Alternative Approaches Using Automation Add-ins

This section briefly discusses the alternative approaches you can take by using Automation add-ins.

Existing Automation Add-ins With UDFs

You can easily use your existing Excel Automation add-ins with Excel Services. Microsoft Visual Studio can automatically generate an interop assembly for an Automation add-in DLL. All you have to do is create a simple managed-code wrapper that exposes an Excel Services UDF for each UDF in the interop assembly, calls it, and then returns its value. Of course, the same constraints that apply to XLLs also apply here in deciding which add-in is a valid candidate for use with Excel Services.

Managed-Code Automation Add-ins

When discussing managed-code function libraries earlier, we recommended that you wrap them with XLL add-ins for use on the Excel client. This is the preferred method in terms of both performance and the best integration with Excel (including new Excel features such as multithreaded calculation).

However, XLL add-in wrappers take effort to develop. In some cases, you might want to consider using managed-code automation add-ins instead. They are much easier to develop but have limitations. Managed-code add-ins have the following limitations:

• Do not take advantage of multithreaded recalculation.

• Slow Excel performance on first use (when they are looked up and loaded).

• Perform more poorly than XLLs because they go through a COM layer for their interface with Excel.

But for some applications, for example, if you only have a few functions, these limitations might be an acceptable trade off for easier development.

For more information about managed-code automation add-ins, see the following blog entries:

• Volatile Managed UDFs (http://blogs.officezealot.com/whitechapel/archive/2005/01/29/4005.aspx)  

• Writing User-Defined Functions for Excel in .NET (https://blogs.msdn.com/eric\_carter/archive/2004/12/01/273127.aspx)

Deploying an XLL with the Managed-Code UDFs It Wraps

In Developing Managed-Code User-Defined Functions, we described how to wrap a managed-code library written in Microsoft Visual C# with an XLL. To make developing and deploying the solution easy, you want to be able to run both the XLL and C# assembly from the same location.

Assemblies are searched in the location of the binary program that is loading them. When Excel loads the XLL, it tries to load the assembly from the folder where the Excel program (Excel.exe) resides, typically in the path drive:\Program Files\Microsoft Office. The options that you have for deploying the assembly are as follows:

• Place the assembly in the Microsoft Office directory.

• name the assembly, and place it in the global assembly cache.

• Tell the common language runtime where to find that particular assembly.

• Load the assembly in a separate application domain, and set its base directory accordingly.

For deployment and development simplicity, the code sample for this article implements the third option.

To do this, the sample uses the AppDomain.AssemblyResolve event to get notifications whenever the common language runtime wants to load a new assembly. Each time a load event occurs, the code checks if this is the assembly it needs to load. If it is, it loads the assembly manually by using the Assembly.LoadFrom method and returns it to the caller.

Conclusion

This article described how to develop UDFs for use in Excel 2007 and Excel Services in Microsoft Office SharePoint Server 2007. It also described how to bridge the gap between Excel 2007 and Excel Services UDF technologies. This article serves as a guide for creating UDF solutions that cross both client and server environments.

About the Authors

is a program manager on the Excel team. He worked on the programmability functionality for Excel Services in Office SharePoint Server 2007.

Shahar Prish is a senior developer on the Excel Services team. He blogs about Excel Services among other topics.

Additional Resources

For more information, see the following resources:

• Data-Type Handling with Excel Services User-Defined Functions

• Extending the Excel Services Programmability Framework

• Excel Services User-Defined Functions

• Developing Add-ins (XLLs) in Excel 2007

• Improving Performance in Excel 2007

• Excel Services Technical Overview

• Creating Custom Solutions with Excel Services

• Using Excel Web Services in a SharePoint Web Part

• Deploying and Optimizing a SharePoint Web Part That Calls Excel Web Services.

• Microsoft Office Developer Center

• Microsoft Office Developer Center: SharePoint Server 2007 Developer Portal

• Blog: Microsoft Excel 2007 and Excel Services

• Blog: Cum Grano Salis

• Blog: LuisBE on Services