How to: Use Excel Add-Ins in the Spreadsheet Control

• Jun 03, 2023
• 2 minutes to read

This example demonstrates how to use Excel add-ins in the SpreadsheetControl. An Excel add-in represents a file containing code that provides additional functionality to Microsoft® Excel® (e.g., custom functions). Add-ins have a variety of practical applications in the business world. For example, the Bloomberg Excel add-in allows end-users to retrieve Bloomberg market, historical and reference data for further analysis in an Excel spreadsheet. The SpreadsheetControl’s custom functions provide the capability to use a variety of Excel add-ins in your spreadsheet application.

In particular, this code sample runs Microsoft® Excel® in the background to evaluate a custom function called SPHEREMASS in the add-in macro code and display the result in a spreadsheet application. The custom function contained in the add-in file calculates the mass of a sphere made of a material with a specified density. If the density is not provided, the density of water is used.

Note

You must have Microsoft® Excel® installed on your computer to run the application.

Implementation

To use Excel add-ins in your spreadsheet application, do the following.

1. For interaction with Microsoft® Excel®, create an instance of the ExcelAppHelper class, which contains the basic methods required for opening an Excel spreadsheet and evaluating macro code in the add-in file.
2. Create an AddInFunction object that represents a custom function. Inherit it from the ICustomFunction base interface, provide a function name (IFunction.Name), and specify the function parameters (IFunction.Parameters) and return type (IFunction.ReturnType). For more details on custom function implementation, refer to the User-Defined Functions (UDF) topic.
3. To provide compatibility with a Microsoft® Excel® application, create the ConvertParameter and ConvertResultValue methods of the AddInFunction class. These methods allow conversion of the SpreadsheetControl’s parameters to an Excel object’s parameters, and vice versa. It is necessary to pass function parameters specified in a spreadsheet application to Microsoft® Excel® for further function calculation, and to display the function results correctly in the SpreadsheetControl.
4. Implement the IFunction.Evaluate method. Within it, call the RunMacros method of the ExcelAppHelper class to run an Excel macro to evaluate a custom function.
5. To use a custom function in a worksheet, add an instance of your function to the collection of workbook custom functions.

View Example: How to use Excel Add-Ins in the Spreadsheet Control