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`

:

| ```
#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`

:

| ```
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:

| ```
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`

:

| ```
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`

| ```
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:

| ```
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.