If you want to do high-performance numerical computation in Excel, you will need to use the FP data type. It is a two dimensional array of double precision floating point numbers defined by Microsoft as follows.

typedef struct _FP
    unsigned short int rows;
    unsigned short int columns;
    double array[1];        /* Actually, array[rows][columns] */
} FP;

The funny looking one element array is what Dennis Ritchie deemed "unwarranted chumminess with the compiler." Our wrapper class (also called FP, but in the xll namespace) uses placement new to make an honest C++ citizen out of this gimmick.

xll::FP a;
assert (a.is_empty());
assert (a.size() == 0);

xll::FP b(2, 3);
assert (b.rows() == 2 && b.columns() == 3);
b[3] = 4; // 1-d index
assert (b(1, 0) == 4); // 2-d index

assert (b.rows() == 3 && b.columns() == 2);
assert (b[3] == 4);
assert (b(1, 1) == 4);

std::sort(b.begin(), b.end()); // STL friendly

Of course Excel knows nothing about this data type so when you get an array passed in from Excel you should declare it as either _FP or ::FP, but not xll::FP.

static AddIn xai_array_unique(
	"?xll_array_unique", XLL_FP XLL_FP,
	"ARRAY.UNIQUE", "Array",
	"STL", "Remove consecutive duplicates from Array."
xll_array_unique(const _FP* pa)
	static xll::FP a;

         // Copy what pa points at to a
	a = *pa;
         // STL function to remove duplicates
	double* pe = std::unique(a.begin(), a.end());
         // Size of non-duplicates
	a.reshape(static_cast<WORD>(pe - a.begin()), 1);

	return a.get();

It is a good idea to declare pointers passed in from Excel as const so the compiler will prevent you from stepping on Excel's memory territory. Note that your xll::FP is declared static because FP::get() returns a pointer to a _FP and that needs to exist after the function call returns so Excel has a chance to copy the array back to its territory.

Last edited Jul 30, 2012 at 7:47 PM by keithalewis, version 8


No comments yet.