Microsoft Office Tutorials and References
In Depth Information
User-Defined Functions (UDFs)
Click the trusted location where UDFs are to be supported. The Trusted Location edit page is
displayed. Scroll to the bottom and select the User-Defined Functions Allowed option.
Calling the UDF
The syntax you use to make a UDF method call from a workbook is essentially the same as the syntax to
call a built-in Excel function. Follow these steps to create the workbook, create a defined name to be used
as a parameter, and make a UDF method call that takes the parameter input as an argument:
Create a new workbook by launching Excel 12 and selecting File
Define Name. For the name, enter String_Input
and set the Refers To field to =Sheet1!$A$1 . Click OK to create the defined name.
3. Make the UDF method call by selecting cell A3 on Sheet1 and typing the following code.
Create a defined name by selecting Formulas
Press Enter. The UDF method call evaluates to #NAME? because Excel doesn’t know about the
Publish the UDF to a trusted file location by selecting File
Excel Services. Type the
trusted file location path in the File Name field. Uncheck the Open In Excel Services box to
deselect this option. Click Save.
Configure the defined name as a parameter and use the EWA to load the workbook by selecting
Excel Services. Click the Excel Services Options link to open the Excel Services
Options dialog box. Click the Parameters tab and then select Add. Click the check box next to the
String_Input entry on the Add Parameters dialog box. Select OK on both the Add Parameters
and the Excel Services Options dialog boxes. In the Save As dialog box, select the Open in Excel
Services option and then click Save.
A browser launches and displays the EWA with the workbook. There is a Parameters pane
on the right side of the EWA, and cell A3 contains Input:.
Enter text in the String_Input parameter field and click Apply. The ECS applies the parameter
value to cell A1, calculates cell A3 by passing the String_Input value to the EchoInput UDF
method, and then returns the new value for cell A3. The EWA reloads and displays the new
values in cells A1 and A3.
ECS XLL UDF
The Excel client supports UDFs in Excel add-ins (or XLLs). This type of UDF implementation is very
common for Excel, and many customers have invested heavily in the XLL type of add-in. As mentioned
earlier in this chapter, you can craft a solution that will wrap existing functionality and essentially create a
UDF solution for the server. Because Excel Services supports only managed UDFs, the XLL requires a
managed wrapper to make the calls to the XLL UDF.