About the Solution

[Previous] [Next]

The Spreadsheet component offers a staggering number of built-in functions, but for many users, this set is one or two functions shy of all the functions they will ever need. Many disciplines have specific functions that are commonly used or that deviate from the Spreadsheet component's built-in functions. Furthermore, some businesses have specific, top-secret calculations that they want to let their employees use. However, those businesses do not want those calculations to be shipped as part of the Spreadsheet component because the calculations give them a technical advantage over their competitors.

To accommodate these cases, the Spreadsheet component allows you to develop custom function add-ins. A function add-in is simply a COM object that supports IDispatch. Once you add the object into the Spreadsheet component (using the AddIn method), every public method on that class becomes a viable function. You or your users can utilize the public method as if it were a built-in function, passing cell or range references to it and using it in the middle of an encompassing formula.

The possibilities are enormous. Custom functions can perform literally any task, from simple calculations to complex Monte Carlo algorithms or other statistical analyses. Custom functions can also access network resources such as databases and core business systems, pulling critical business data into your spreadsheet models. Furthermore, custom functions can retrieve data from other data sources such as your e-mail inbox, your contacts list, a local lookup file—anything you can accomplish in a COM object can be exposed to the Spreadsheet component as a custom function.

It is not terribly hard to implement simple function add-ins, but as soon as you want to work on ranges of cells or require a specific editor to help users build a parameter list for your function, implementation can get a bit more complex. The solution in this chapter is a custom function add-in that contains some simple example functions; some more powerful, range-based analysis functions; and a function designed to look up a cell value from an OLAP data source, complete with its own function-editing user interface. These illustrate the various types of functions you might want to implement in a custom function library, providing you with a starting point from which to implement your own custom functions.