11,796,394 members (70,195 online)

# Sample Excel Function Add-in in C

, 21 Dec 2004 282.5K 7.3K 76
 Rate this:
How to program an Excel XLL add-in in C.

## 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 Yi corresponding to some arbitrary value Xi. 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 Xi 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.

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

## About the Author

 Engineer United States
No Biography provided

## Comments and Discussions

 First PrevNext
 Passing Array or single cells to a function Member 969930121-Apr-14 7:31 Member 9699301 21-Apr-14 7:31
 Unable to download files Sir David1-Oct-13 20:59 Sir David 1-Oct-13 20:59
 Re-compile for 64 bit and OSX 10.7 Lion? J. Ortiz23-Jan-12 3:06 J. Ortiz 23-Jan-12 3:06
 giving only 1st value of arrey malayhk23-Oct-09 2:37 malayhk 23-Oct-09 2:37
 Re: giving only 1st value of arrey earthman10113-Nov-09 13:11 earthman101 13-Nov-09 13:11
 Re: giving only 1st value of arrey earthman10113-Nov-09 13:25 earthman101 13-Nov-09 13:25
 Re: giving only 1st value of arrey malayhk16-Nov-09 22:46 malayhk 16-Nov-09 22:46
 Thanks and Question about Interp Shutting Down Excel John Bruno25-Sep-09 11:24 John Bruno 25-Sep-09 11:24
 Re: Thanks and Question about Interp Shutting Down Excel earthman10113-Nov-09 13:12 earthman101 13-Nov-09 13:12
 Re: Thanks and Question about Interp Shutting Down Excel earthman10113-Nov-09 13:26 earthman101 13-Nov-09 13:26
 Re: Thanks and Question about Interp Shutting Down Excel - 2 John Bruno14-Nov-09 3:20 John Bruno 14-Nov-09 3:20
 Interp works with Excel 2007!!! Mark Swift10-Jul-09 5:54 Mark Swift 10-Jul-09 5:54
 Interp for Excel 2007 Mark Swift2-Jul-09 8:54 Mark Swift 2-Jul-09 8:54
 InterpX function (from Interp32.xll Excel addin) limitation M.Slipper18-Sep-08 10:28 M.Slipper 18-Sep-08 10:28
 how do I get two dimensional aray from excel? Dang Vu Tuan15-Sep-08 16:38 Dang Vu Tuan 15-Sep-08 16:38
 Re: how do I get two dimensional aray from excel? abhiram_nayan27-Jan-09 23:14 abhiram_nayan 27-Jan-09 23:14
 How do you add descriptions to function arguments? kenmayer29-Nov-07 3:30 kenmayer 29-Nov-07 3:30
 Tooltips Gonzus2-Nov-07 5:25 Gonzus 2-Nov-07 5:25
 Re: Tooltips Gonzus2-Nov-07 5:46 Gonzus 2-Nov-07 5:46
 How to create the help file - INTERP.HLP? xutuan26-Sep-07 7:24 xutuan 26-Sep-07 7:24
 Re: How to create the help file - INTERP.HLP? Titto Sebastian23-Oct-08 20:27 Titto Sebastian 23-Oct-08 20:27
 Re: How to create the help file - INTERP.HLP? tozy23-Oct-08 20:34 tozy 23-Oct-08 20:34
 Error w/ Interp function for Excel 2007 rkhetia16-Jul-07 10:48 rkhetia 16-Jul-07 10:48
 write a vba function wescD20-Sep-07 11:04 wescD 20-Sep-07 11:04
 Problems adding the interp application This is a pain3-Jul-07 13:56 This is a pain 3-Jul-07 13:56
 Re: Problems adding the interp application SpottyLayton24-Jul-07 4:42 SpottyLayton 24-Jul-07 4:42
 Re: Problems adding the interp application This is a pain24-Jul-07 7:20 This is a pain 24-Jul-07 7:20
 Re: Problems adding the interp application SpottyLayton24-Jul-07 10:04 SpottyLayton 24-Jul-07 10:04
 Re: Problems adding the interp application IT-Cisco20-Nov-07 14:26 IT-Cisco 20-Nov-07 14:26
 Re: Problems adding the interp application me6846844-Mar-08 21:02 me684684 4-Mar-08 21:02
 Add in description????? mousam.dubey20-Apr-07 4:42 mousam.dubey 20-Apr-07 4:42
 Works fine on my machine, but not on others??? JoeDownloadGuy2-Jan-07 3:50 JoeDownloadGuy 2-Jan-07 3:50
 Re: Works fine on my machine, but not on others??? xcwq2-Jan-07 17:26 xcwq 2-Jan-07 17:26
 Re: Works fine on my machine, but not on others??? Neal Watts22-May-08 10:56 Neal Watts 22-May-08 10:56
 xll add in toolbar joe1521-Jun-06 5:23 joe15 21-Jun-06 5:23
 xlw plus markjoshi4-Jun-06 18:06 markjoshi 4-Jun-06 18:06
 Not a valid add-in fred_sl23-May-06 12:06 fred_sl 23-May-06 12:06
 Re: Not a valid add-in Eric Tham20-Jul-06 2:34 Eric Tham 20-Jul-06 2:34
 Function returning #NAME? eLiNK778-Dec-05 4:57 eLiNK77 8-Dec-05 4:57
 Add-in in C++ Zoltan Hegyi27-Jun-05 2:08 Zoltan Hegyi 27-Jun-05 2:08
 Re: Add-in in C++ Mayank Ji22-Nov-05 19:06 Mayank Ji 22-Nov-05 19:06
 Re: Add-in in C++ hz106724-Nov-05 3:03 hz1067 24-Nov-05 3:03
 interp2 code of matlab in C Anonymous2-Jun-05 17:19 Anonymous 2-Jun-05 17:19
 Problems adding interp application carlos marin28-Apr-05 6:06 carlos marin 28-Apr-05 6:06
 Re: Problems adding interp application Chuck Nelson22-Feb-06 4:07 Chuck Nelson 22-Feb-06 4:07
 Re: Problems adding interp application Eric Tham20-Jul-06 2:35 Eric Tham 20-Jul-06 2:35
 Can a Debugger be used + Value Returned by Function Name Entry JSadleir9-Apr-05 14:29 JSadleir 9-Apr-05 14:29
 excel4 (slightly ot) Matias_N10-Jul-04 6:38 Matias_N 10-Jul-04 6:38
 XLL using C#? quant20046-Jul-04 5:19 quant2004 6-Jul-04 5:19
 Re: XLL using C#? JChampion8-Jul-04 18:39 JChampion 8-Jul-04 18:39
 Last Visit: 31-Dec-99 18:00     Last Update: 6-Oct-15 11:01 Refresh 12 Next »

General    News    Suggestion    Question    Bug    Answer    Joke    Rant    Admin

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.