Configuring User-Defined Function Assemblies

As part of the focus on security built into SharePoint Server, ECS has been designed to prevent users from executing potentially malicious code inside Office Excel 2007 workbooks. As a result, many of the automation features used in workbooks on the desktops will not run on the server. Such features as Active X controls, VBA macros, links to other spreadsheets, workbooks with add-in references, and DDE links will generate an error in Excel Calculation Services. In place of code embedded in the workbook, Excel Calculation Services supports custom code libraries installed on the server and configured as trusted libraries. A User-Defined Function is a code module compiled as managed code under the Microsoft .NET Framework version 2.0. User-defined functions written to run in the Office Excel 2007 client cannot be used directly by Excel Calculation Services. A library that is configured to run under Excel Calculation Services is known as a User-Defined Function Assembly. These assemblies can be installed into a folder on the server, into a network share accessible by the server, or into the Global Assembly Cache (GAC) on the server. Once the assembly is installed on the server, it must be trusted within Excel Calculation Services.

Configuring a User-Defined Function Assembly

1. Open Central Administration from the Start menu.

2. Click on the link for the Shared Services Provider.

3. Under Excel Services Settings, click User-Defined Function Assemblies.

4. Click Add User-Defined Function (UDF) Assembly.

5. Under the Assembly field, as shown in Figure 11-9, enter the unique identifier for the UDF file.

6. For a local file, type the full path: for example, D:\ExcelUDFs\CustomFunctions.dll or \\ExcelServer\ExcelUDFs\CustomFunctions.dll.

7. For an assembly in the GAC, type the Strong Name: for example, CustomFunctions, Version=1.1.0.0, Culture=Neutral, PublicKeyToken=d7012336c6ae8fd27.

8. Select the Assembly Location option that corresponds with the path-either GAC or Local File.

9. Click OK.

Figure 11-9: Adding the User-Defined Function Assembly.