In the last post I described how to set up a Visual Studio solution to build xll addins using XLW. For the introduction to XLW series take a look at the first post. Today I am going to show how easy it is to create user defined functions (UDF) for Excel using XLW.

The starting point will be the solution we set up in the previous post. If you don’t have set it up you can download it.

The project contains two files that we are going to modify to add new functions:

  • mylib.h 

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

    #ifndef _mylib_h_

#define _mylib_h_


#include <xlw/xlw.h>


//<xlw:libraryname=MyLibrary

double //Square root of an argument
MySqrt(double x //argument
       );

#endif

  • mylib.cpp 

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

    #include "mylib.h"

#include <math.h>


double MySqrt(double x)
{
  if( x < 0. )
  {
    throw("#Negative argument");
  }
  return sqrt(x);
}

The third file (xlwmylib.cpp) is autogenerated by the build process.

1. Library Name

Library Name is a term XLW uses to tell in which category you can find your functions in the Function Wizard: \
my library in wiz\
The name of the library is specified in the file mylib.h on the line 6: 

//<xlw:libraryname=MyLibrary

2. Basic Function

Consider a function definition, which should be placed in mylib.h file:

1
2
3
4

double //Sum of square roots of two numbers
SumSqrt(double x, //argument 1
  double y //argument 2
  );
{:.dl-lines}
Line 1
contains type of results the function returns and comment with function description as it will appear in Excel’s function wizard
Line 2
contains the name of the function (Function in Excel will have same name), and definition of the first argument. The comment is a description of the argument as it will appear in Excel’s function wizard. Note: if you need to include quotes in description string escape them with backslash, e.g.: // argument \"1\"
Line 3
contains the second argument with its description
Line 4
ends function definition

3. Handling Errors

It is possible to return an error message to Excel from a function. Consider the example with SumSqrt function. Put the following code in mylib.cpp:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

#include "mylib.h"

#include <math.h>


double SumSqrt(double x, double y)
{
  if( x < 0 || y < 0 )
  {
    throw("#Negative argument");
  }
  return sqrt(x) + sqrt(y);
}
{:.dl-lines}
Line 6
check if any of the arguments is negative
Line 8
if we have a negative argument we throw an error with message #Negative argument

As a result, the Excel function displays string #Negative argument if any of the arguments is negative or numeric result if both numbers are non-negative.

4. Basic input/output data types

  • double
  • bool
  • std::string
  • int - although it can be used as an input type it cannot be an output type. Convert it to double for output.

5. Happy Coding!

If you want to see the code described in this post in action you can download updated mylib library. It also contains changes introduced in the next post.