Create a User-Defined Function Reference

Solution: Use the New-SPExcelUserDefinedFunction cmdlet.

The New-SPExcelUserDefinedFunction cmdlet can be used to create a reference to an assembly that can call user-defined functions within Excel workbooks running under Excel Services. Provide the assembly information and the Excel Services Application instance, as shown in Listing 21.17.

Listing 21.17. Creating a New User-Defined Function Reference

New-SPExcelUserDefinedFunction -ExcelServiceApplication "Excel Services
Application" -Assembly "MyCompany.ExcelServices.UDF,
Version=1.0.0.0, Culture=neutral, PublicKeyToken=82e6dce111e9429c"
-AssemblyLocation GAC

The -AssemblyLocation can be one of the following:

• GAC

• LocalFile

For security as well as central access, user-defined functions should be compiled into a DLL and placed in the GAC.