Implementing Simple Functions

[Previous] [Next]

In this chapter, I will use the term "simple functions" to describe functions that take only scalar values (not ranges) for inputs and do not need a custom function-editing user interface. These are the most common functions, and thankfully, they are quite easy to implement. Let's take a look at the two simple functions I implemented in the custom function library: DoubleIt and DateAdd.

DoubleIt—The "Hello World" of Custom Functions

Every programming book has a "Hello World" demo in it somewhere, and this is mine. To illustrate defining a custom function in the simplest context possible, I wanted the first function we discuss to be so trivial that you will not focus on the implementation of the function itself. The DoubleIt function merely multiplies the input value by 2, catching the appropriate error conditions:

 Public Function DoubleIt(Number As Variant) As Variant     On Error GoTo Err_DoubleIt          If IsNumeric(Number) Then         DoubleIt = Number * 2     Else         ' Return #NUM! if not numeric         DoubleIt = "#NUM!"     End If     Exit Function Err_DoubleIt:     DoubleIt = "#VALUE!"     Exit Function End Function 'DoubleIt() 

To call this function in a cell formula, use the following formula syntax:

 =DoubleIt(C3) 

How Can I Use My Existing XLL Add-Ins in the Spreadsheet Component?

Long ago, Microsoft Excel defined a model for custom functions and add-ins referred to as XLLs, which are DLLs that implement certain function entry points so that Excel can generically inspect the DLLs and invoke functions exposed from them.

The Spreadsheet component uses the COM standard to enable function add-ins. If you have existing XLLs that expose custom functions, you can easily use these functions with the Spreadsheet component by wrapping them in a COM object. You do not need to reimplement your functions—you only need to repackage them as a COM object.

If you specify a cell reference as an input argument to your custom function, the Spreadsheet component will pass the current value of that cell to your custom function. The Spreadsheet component will also note that the cell containing your function is dependent on the referenced cell (C3, in this case) and will automatically recalculate the function whenever the referenced cell's value changes.

As you can see, defining a custom function is as easy as defining a public function in a class module. However, I want to impart a few tips for implementing a good custom function.

First, the Spreadsheet component is Variant-based, meaning that all cell values are inherently Variants. A cell can just as easily contain a string, a date, or a Null, Empty, or Error value as it can contain a number. Therefore, it is typically better to type your function input arguments as Variants and check their type and value in your code than to rely on the Spreadsheet component to convert the cell value to the type you declare. If you type an input argument as a stronger type such as String or Long, the Spreadsheet control will attempt to coerce the input value into that type before calling your function. If the Spreadsheet control cannot coerce an input value into the type you request, the control will resolve the function to "#VALUE!" without ever calling your function. If you want to handle those conditions in a more graceful way, type your input parameters as Variants and return a more appropriate value if the input is not the required type. You can use the VBA functions that start with "Is" to determine whether the Variant is a certain type.

Second, it is helpful to include an error handler in any custom function. The Spreadsheet control will gracefully handle any errors generated by a custom function, displaying "#VALUE!" and ignoring the error, but you might want to display the error text in a fashion that lets your users know the nature of the failure. In the OLAPLookup function discussed later in the chapter, I return the Err.Description property in the event of an error so that the error description text is actually inserted into the cell.

The last tip is that you can return any of the error strings (such as "#VALUE!") to set the cell value to an error. Other error values include "#NULL!" for returning a Null value, "#NUM!" to indicate a problem with an input argument, and "#N/A!" to indicate that some information is missing or the function's result is not applicable given the inputs or current conditions.

Exposing Powerful VBA Functions

There are many kinds of simple functions you might want to implement, but often, the function you need is already implemented in the programming language you use to create your COM object. For example, VBA exposes many complex and highly useful functions for working with dates, strings, and even the file system. You can expose these functions to the Spreadsheet component by simply wrapping them in a public method of your COM class. For example, let's look at the DateAdd function in the CustomFunctions.cls file:

 Public Function DateAdd(Interval As String, Number As Double, _ StartDate As Date) As Variant     On Error GoTo Err_DateAdd          If IsDate(StartDate) Then         DateAdd = VBA.DateAdd(Interval, Number, StartDate)     Else         ' Return #NUM! if not a date         DateAdd = "#NUM!"     End If     Exit Function Err_DateAdd:     DateAdd = "#VALUE!"     Exit Function End Function 'DateAdd() 

This function simply wraps the VBA version of the function (called by using the VBA library qualifier), performing some initial error checking and returning the appropriate error values. You can use this same technique to expose any programming language's functions or any other function not already exposed as a method of a COM object. To try this, run the project in Visual Basic and adjust the various inputs to DateAdd in the Simple Functions section of the test spreadsheet. Try setting the Interval value to "yyyy" to see the result of adding a number of years to the start date.

A custom function can return only a scalar value, meaning you cannot return other COM objects, arrays, structures, or binary data. If you do, the Spreadsheet component will either display "#NAME!" or the token "<Binary Data>". The rule of thumb is that if you can type the value into the cell, you can return it from a custom function. Although it would be nice to load a range of cells with an entire table of data, you can do this through the programming model only by using the Range object, not by returning a value from a custom function.