In the last post I described how to write simple functions using XLW. For the introduction to XLW series take a look at the first post. Today I am going to show how write functions that can input/ouput arrays, vectors, matrices or general ranges.

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.

1. Numeric array input/output

The basic types for input or output arrays or matrices of numbers are:

  • MyMatrix
  • MyArray

The difference is that MyMatrix is two dimensional and MyArray is one dimensional. MyArray is just a typecast to std::vector<double>.

Here is an example function that takes an array and produces the matrix equal to outer product of the array with itself:

Add the following to mylib.h: 

1
2
3
4
5
6
7

#include <xlw/MyContainers.h>


using namespace xlw;

MyMatrix // Outer product of array with itself
Outer1(const MyArray& a //array a
       );

Add the following to mylib.cpp: 

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

MyMatrix Outer1(const MyArray& a)
{
  MyMatrix res(a.size(),a.size());
  for(size_t i = 0; i < a.size(); ++i)
  {
    for(size_t j = 0; j < a.size(); ++j)
    {
      res[i][j] = a[i]*a[j];
    }
  }

  return res;
}

MyArray and MyMatrix Gotchas:

  • XLW does not flatten input, so if you try to supply two dimensional cell range for MyArray argument, the call to that function will fail. Only one dimensional column or row range can work for MyArray.
  • Although both horizontal and vertical cell ranges can be supplied as values for MyArray, the output MyArray is always a column range. So if you need to output horizontal one-dimensional array use MyMatrix with one row.
  • If a user tries to supply a range with data incompatible with MyMatrix or MyArray, the function does not run and returns with the message that it failed to convert input range.
  • The input array types should be defined const and passed by referenced (i.e. const MyArray&).
  • Debug note: If during debug of your code the debugger breaks with a message User breakpoint called from code at ... and Assembly is shown, it means that somewhere in the code the MyMatrix or MyArray was written beyond defined bounds. I.e. the following code would produce such an error:
1
2
3
4
5
6

MyArray f()
{
  MyArray a(5);
  a[5] = 1.; // maximum index for a is 4
  return a;
}

The error is signaled at line 5 (when the function returns), rather than at line 4 (when out of range assignment takes place) - this makes finding this bug tricky.

2. Input/output matrices of mixed types

CellMatrix or XlfOper can be used to input or output matrices of mixed types. XlfOper is preferable since it does not need to copy data into temporary memory, which is especially important for big ranges.

XlfOper has the following methods which allow querying data directly from Excel:

  • Inspectors:
    • IsMissing() - used by Excel to flag a parameter that hasn’t been supplied
    • IsError() - is the data an error?
    • IsRef() - is the data a reference?
    • IsSRef() - is the data a sheet reference?
    • IsNumber() - is the data a number?
    • IsString() - is the data a string?
    • IsNil() - is the data an empty cell?
    • IsBool() - is the data a boolean?
    • IsInt() - is the data an integer?
    • IsMulti() - is the data a matrix?. If multi can use the following methods:
      • rows() - number of rows in the matrix
      • columns() - number of columns in the matrix
      • (i, j) - can use bracket notation to set/retrieve values from the matrix
  • Conversions:
    • AsDouble() to double
    • AsRef() to XlfRef
    • AsShort() to short
    • AsBool() to bool
    • AsInt() to int
    • AsULong() to unsigned long
    • AsString() to char*
    • AsWstring() to std::wstring
    • AsDoubleVector() to std::vector<double>
    • AsArray() to MyArray
    • AsMatrix() to MyMatrix
    • AsCellMatrix() to CellMatrix

Example

Add the following to mylib.h: 

1
2
3

XlfOper //Squares all numbers and sets all other cells to \"-\"
SquareNumbers(XlfOper& input //input range
  );

Note: Input XlfOper& argument should not be const, because otherwise you won’t be able to access its elements with (i, j) notation.

Add the following to mylib.cpp 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

XlfOper SquareNumbers(XlfOper& input)
{
  if( !input.IsMulti() )
  {
    XlfOper res;
    if (input.IsNumber())
    {
      const double x = input.AsDouble();
      res = x * x;
    }
    else
    {
      res = "-";
    }
    return res;
  }

  XlfOper res(input.rows(), input.columns());
  for (size_t i = 0; i < res.rows(); ++i)
  {
    for (size_t j = 0; j < res.columns(); ++j)
    {
      if(input(i,j).IsNumber())
      {
        const double x = input(i, j).AsDouble();
        res(i, j) = x*x;
      }
      else
      {
        res(i, j) = "-";
      }
    }
  }
  return res;
}

Here is another example how you can populate output range: 

1
2
3
4
5
6

XlfOper ret(3, 2);
ret(0, 0) = "abc";
ret(0, 1) = (short)42;
ret(1, 0) = 1.23;
ret(1, 1) = XlfOper::Error(xlerrValue);
ret(2, 0) = true;

Update 08/19/2016 Gotcha Sometimes, when you use two dimensional XlfOper as a return from your function, instead of your output you see #N\A in Excel cells. This happens when one or more of the cells of XlfOper are left not initialized. For instance if in the example above you remove the second line (ret(0, 0) = "abc";) the output in Excel will contain #N\A in every cell (not just in the first one).

3. Happy Coding!

If you want to see the code described in this post in action you can download updated mylib library.