Export (0) Print
Expand All

Excel Services User-Defined Function

SharePoint 2007

Represents server-side managed code custom functions that extend the built-in library of functions, which Excel workbook authors can call using formulas.

Real World Example

A business group needs to package a common set of complex calculations into a reusable object. Their developer creates a new package of user-defined functions (UDFs) for Excel Calculation Services, and then has it installed and trusted within Excel Services.

Developers create custom calculation packages to provide:

  • Functions that are not built into Excel.

  • Custom implementations to built-in functions.

  • Custom data feeds for legacy or unsupported data sources, and application-specific data flows.

Technical Details

UDFs can return a variety of simple values, and can also return object arrays of values that can be used to return rows of data queried from an external data source. This can allow UDFs to query nonstandard data sources such as Web services to return new sets of data.

The supported return value types are as follows:

  • Numeric types: Double, Single, Int32, UInt32, Int16, UInt16, Byte, Sbyte

  • String

  • Boolean

  • Object arrays: one- or two-dimensional arrays, that is, object [], object [,], int[] and int[,])

  • DateTime

  • Object

For more information about data types, see Frequently Asked Questions About Excel Services UDFs.

Excel Services architecture

For more information about Excel Services managed-code UDF, see Understanding Excel Services UDFs.

By default, UDF assemblies run with full trust, but in a hosted environment they should be limited to using the Microsoft .NET Framework 2.0 Configuration tool. For details on how to restrict a UDF to run with lower permissions, see How to: Restrict UDF Code Access Security Permissions.

You can find detailed technical details for developers, including sample code for UDFs in the Excel Services User-Defined Functions section of the Microsoft Office SharePoint Server 2007 Software Development Kit (SDK).

Support Details

UDF assemblies are disabled by default. You must set a trusted location for Excel workbooks in the Shared Services Provider (SSP) that is hosting Excel Services, and you must set the AllowUdfs flag in the trusted location for the workbook to reference any custom UDFs. You can set this flag by using the User-defined functions allowed option on the Excel Services Trusted File Locations page.

Because UDFs have full trust by default, they can be configured to read or update data on a back-end server running Microsoft SQL Server. You should consider this a potential risk factor if the code is running with a process account that has access to any backend database.

After being called, UDF assemblies are locked, making it impossible to update them on disk until the process that called them frees the lock. This occurs whenever the application pool process used by the SSP that is hosting Excel Services is recycled. You can use the following three ways to recycle the application pool process:

  • Call iisreset to restart the entire Internet Information Services (IIS) server.


    Resetting IIS will end all current sessions.

    For more information, see How to: Enable UDFs.

  • Use the IIS console to recycle the appropriate SSP application pool under the Application Pools node.

  • Use the IISAPP script to recycle the app pool (IISAPP /a SharedServicesApplicationName /r).

For more information, see Unloading an Application from Memory (http://msdn2.microsoft.com/en-us/library/ms943779.aspx).

Community Additions

© 2014 Microsoft