Sample Excel Function Add-in in C

• Download source files - 20.2 Kb
• Download XLL and help files - 12.9 Kb

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;                   // xltypeNum
      LPSTR str;                    // xltypeStr
      WORD bool;                    // xltypeBool
      WORD err;                     // xltypeErr
      short int w;                  // xltypeInt
      struct
      {
         WORD count;                // always = 1
         XLREF ref;
      } sref;                       // xltypeSRef
      struct
      {
         XLMREF far *lpmref;
         DWORD idSheet;
      } mref;                       // xltypeRef
      struct
      {
         struct xloper far *lparray;
         WORD rows;
         WORD columns;
      } array;                      // xltypeMulti
      struct
      {
         union
         {
            short int level;        // xlflowRestart
            short int tbctrl;       // xlflowPause
            DWORD idSheet;          // xlflowGoto
         } valflow;
         WORD rw;                   // xlflowGoto
         BYTE col;                  // xlflowGoto
         BYTE xlflow;
      } flow;                       // xltypeFlow
      struct
      {
         union
         {
            BYTE far *lpbData;      // data passed to XL
            HANDLE hdata;           // data returned from XL
         } h;
         long cbData;
      } bigdata;                    // xltypeBigData
   } 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] =
{
//    function title, argument types, function name, arg names,
//        type (1=func,2=cmd),
//        group name (func wizard), Hot Key, help ID,
//        function description,
//        (repeat) description of each argument

    {" 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:

1. The name of the XLL
2. The function name as it is declared in the C code (i.e., "Interp")
3. The argument types, starting with the function return value itself. (R=XLOPER (variant), A=boolean, B=double, see dev kit for more.)
4. The name of the function as it should appear in the function wizard list.
5. A string listing the argument names to the function (for user info only).
6. The function type; 1=function (returns a value), 2=command.
7. 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.
8. The hotkey assignment for commands.
9. The filename and help ID for this function.
10. A description of the function itself.
11. 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;

//where:
//    X = X argument
//    xlo, xhi = bounding X values
//    ylo, yhi = Y values corresponding to bounding X values

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.