## Introduction

This is a sample XLL (DLL for Microsoft Excel) that adds linear interpolation capability to the Excel function list. XLL functionality in Excel is extremely fast, and allows you to extend the basic functions in any way you desire. This XLL was written to address a problem in which I was doing many thousands of interpolations via VBA in a spreadsheet, and it was taking about 20 minutes to calculate (for some reason, linear interpolation is missing from the Excel built-in function set). Inclusion of this interpolation function to replace the VBA code cut down the calculation time to a matter of seconds. Note that speed is not the only advantage; functions installed in an XLL can be permanently included in Excel so they are always available and easy to access.

## Background

Excel add-ins (particularly in C) are somewhat of a mystery, with documentation hard to come by. It was originally documented (sort of) in the Microsoft Excel Developer's Kit (available here). However, when originally developing this add-in, I found that the documentation was unclear about many of the important details. I have gotten so much from The Code Project -- I figured that this would be a good way for me to contribute back a little.

This particular add-in does linear interpolation on a set of data in Excel. Given a curve that is defined by a table of discrete (X,Y) value pairs, interpolation is the process of estimating a dependent (Y) value along the curve that is not necessarily at the defined points.

For example, given the chart below, in which an X and Y pair of values is known at each of the dots, linear interpolation will calculate the value of Y_{i} corresponding to some arbitrary value X_{i}. Note that the term linear implies that the data points are connected by straight lines.

The function can also handle extrapolation by assuming the data continues in a straight line beyond the first two or last two points.

The syntax of the interpolation function is as follows:

`Interp( X, Xarray, Yarray, extrapFlag )`

where:

`X`

: the X_{i} argument.
`Xarray`

: the list of X values that define the curve.
`Yarray`

: the list of Y values that define the curve.
`extrapFlag`

: boolean value; `true`

to allow extrapolation, `false`

to disallow extrapolation.

## Using the code

The XLL is essentially a function that just waits to be called by Excel whenever the spreadsheet recalculates. The XLL will add new functions to the Excel function list. For example, the following function could be entered into a cell:

=INTERP(A1,B1:B10,C1:C10)

The function can be used in arbitrarily complex formulas, e.g.:

=2.564*D15-3.543*INTERP(A1,B1:B10,C1:C10)^2.7

And the function can be used as often as desired in the spreadsheet. Data are transmitted to and from Excel using the `XLOPER`

`struct`

, which really just encapsulates a variant structure with a C `union`

declaration. A basic XLL framework is supplied by Microsoft here. This framework includes all the required include files, libraries, and some basic code to build an XLL (however, I did not find it very straightforward to use).

The `XLOPER`

declaration looks like this:

typedef struct xloper
{
union
{
double num; LPSTR str; WORD bool; WORD err; short int w; struct
{
WORD count; XLREF ref;
} sref; struct
{
XLMREF far *lpmref;
DWORD idSheet;
} mref; struct
{
struct xloper far *lparray;
WORD rows;
WORD columns;
} array; struct
{
union
{
short int level; short int tbctrl; DWORD idSheet; } valflow;
WORD rw; BYTE col; BYTE xlflow;
} flow; struct
{
union
{
BYTE far *lpbData; HANDLE hdata; } h;
long cbData;
} bigdata; } val;
WORD xltype;
} XLOPER

This `struct`

can be used to pass floating point values, integers, strings, error codes, arrays, etc.

## Generic.c

The XLL code is first initialized via the `DllMain()`

entry point. This function is called by Windows only once, when the DLL is first loaded. In our case, this routine just initializes some Pascal strings (which require a byte count in the first byte). We only respond to the `DLL_PROCESS_ATTACH`

reason code. Once the strings are initialized, the code just waits to be called by Excel.

The code is "hooked" into Excel using a registration process. Excel will call the function `xlAutoOpen()`

when the XLL is added to Excel (via Add-in Manager, REGISTER command, VBA, etc.). The `xlAutoOpen()`

routine then "registers" each function to Excel. Excel provides `Excel4()`

and `Excel4v()`

(varargs version) to provide access to a wide range of functionality. The first argument to this routine is a function code, which determines the behavior (and the remaining arguments). `xlAutoOpen()`

first calls `Excel4(xlGetName)`

to get the XLL name. This name is then used as an argument to the `xlfRegister`

function code, which registers each of our functions. The `xlfRegister`

option passes information about the name of each function, its passed parameter types, its return type, its help ID, a description of the function, and a description of each parameter. This information is used by Excel to give tooltip help when the function is entered in a spreadsheet, and helpful text when the insert function wizard is used. Take a look at the file '*interface.h*' for a listing of the function information:

LPSTR functionParms[kFunctionCount][kMaxFuncParms] =
{
{" Interp", " RRRRA", " Interp",
" x,xArray,yArray,extrapFlag", " 1",
" Interpolation Add-In", " ",
" interp.hlp!300",
" Performs linear interpolation. This is the general version"
"that can handle single values or arrays.",
" The X values to be interpolated. Can be a single value or"
" an array (each value is interpolated individually)",
" A table of values that define X. Must be sorted (increasing"
" or decreasing).",
" A table of values that define Y (for each X table value).",
" If TRUE, extrapolation beyond the table is allowed. If omitted"
" or FALSE, the result is truncated at the table bounds." },
{" InterpX", " RBRRA", "InterpX",
"x,xArray,yArray,extrapFlag", " 1",
" Interpolation Add-In", " ",
" interp.hlp!310",
" Performs linear interpolation. This version interpolates"
" only a single X value at a time.",
" The X value to be interpolated. Only a single value is allowed"
" to take advantage of Excel's 'implicit intersection'.",
" A table of values that define X. Must be sorted (increasing"
" or decreasing).",
" A table of values that define Y (for each X table value).",
" If TRUE, extrapolation beyond the table is allowed. if omitted"
" or FALSE, the result is truncated at the table bounds." },
};

The information passed to Excel is as follows:

- The name of the XLL
- The function name as it is declared in the C code (i.e., "Interp")
- The argument types, starting with the function return value itself. (R=XLOPER (variant), A=boolean, B=double, see dev kit for more.)
- The name of the function as it should appear in the function wizard list.
- A string listing the argument names to the function (for user info only).
- The function type; 1=function (returns a value), 2=command.
- The name of the category that this function belongs in (e.g., "Financial", "Math & Trig", etc.). You can make up a new name, or use an existing Excel category.
- The hotkey assignment for commands.
- The filename and help ID for this function.
- A description of the function itself.
- A description of each argument to the function.

Note that this add-in actually contains two versions of the `Interp()`

function. The first, `Interp()`

, is a general version that can handle Excel arrays for the 'X' input argument. That is, it can receive multiple X values and return multiple Y values (one for each X). This provides ultimate flexibility. However, there is a feature in Excel called "implicit intersection" that allows you to use range names in formulas in a simplistic way. For example, you might declare a spreadsheet range with the name 'foo' and the range C1:C10. If you then use the name 'foo' in a spreadsheet formula that expects a single value, Excel will automatically use the value that corresponds to the row or column that matches the formula location. That is, if the formula is in A3, then using the name 'foo' will yield a value from cell C3 (same row). If you are trying to use implicit intersection with the general` Interp()`

routine, it will try to calculate an array of results since the whole array will be passed to `Interp()`

for the X argument. Therefore, a second version called `InterpX()`

is provided which only accepts a single value for the X argument, and therefore works with implicit intersection.

The other function of note in *generic.c* is the `xlAutoAdd()`

function, which is called just once at the time that the add-in is added via the add-in manager. This gives you an opportunity to pop up a dialog box indicating copyrights, etc.

## Interp2.c

The *Interp2.c* file contains the function code itself. The code is well-commented, but here's the lowdown. Upon entry to `Interp()`

, the code first verifies that the `Xarray`

argument type is acceptable. Since it accepts the `XLOPER`

(variant) type, it is necessary to make sure that the type the user entered to the function is reasonable. The type must be a reference array (`xlTypeRef`

or `xlTypeSRef`

) or a multi array (`xlTypeMulti`

). `Excel4(xlCoerce)`

is then called to "coerce" the data into a `xlTypeMulti`

type so that the subsequent calculations only have to deal with one type. Next, a check is made to see if any of the `Xarray`

data has not yet been calculated by the spreadsheet. If so, just return and wait for later. This procedure of data checking is repeated for the `Yarray`

data. Next, a check is done to make sure that the X data are monotonically increasing or decreasing (sorted), and that the `Xarray`

and `Yarray`

data are all numeric values. The final check is on the X argument, which is similar to the `Xarray`

and `Yarray`

arguments.

After all the input arguments have been verified, the interpolation calculations are done. This is just a process of finding which two values in the `Xarray`

argument are the bounds around the X argument (for interpolation). For extrapolation, the code determines if the X argument is less than the first `Xarray`

value or greater than the last one. The interpolation calculation is simple:

result = ( X - xlo ) / ( xhi - xlo ) * ( yhi - ylo ) + ylo;

One more note of interest -- you may need to allocate memory to return values to Excel. This will then need to get freed up using the `xlAutoFree()`

function. Excel will call this function if you return an `XLOPER`

`struct`

with the `xlbitDLLFree`

bit set. Also, sometimes Excel will need to free up memory that it allocates when it passes parameters to you. You can free that memory using the `Excel4( xlFree )`

function call.

## Installation

This XLL add-in includes a help file and an XLL file. To add it into Excel, put *interp32.xll* into the *Program Files/Microsoft Office/Office10/Library* folder. Put the help file *interp.hlp* into the *Program Files/Microsoft Office/Office10* folder. This will make the help available from the Help button in the function wizard. To make the add-in available in Excel, select the "Interpolation Add-in" check box under the Tools/Addins menu. This only needs to be done once and the added functionality will remain available in Excel permanently (unless it is later disabled).

## Update

This code was originally written for Visual C++ 6.0. Part of the initialization process requires adding a byte count to the beginning of some static strings, which is allowed in Visual C++ 6.0, but causes an exception in later versions. This article has now been updated to correct that problem (plus two other minor problems). The solution file is now set up for Visual Studio 2002 .NET.