Returning vectors to Excel

This topic shows you how to return a 1-dimensional array to Excel. The example is a very simple function for sorting numeric arrays in ascending order.

Use the Function Wizard to create a new function with these properties:

Name:	SORTVECTOR
Category:	Lookup & Reference
Description:	Sort a vector of numbers in ascending order

Add a single argument with the following properties:

Name:	Vector
Type:	Double
Dimensions:	Vector
Description:	Unsorted vector of numbers

Press OK to return to DevStudio.

Add code so that your add-in function looks like this:

#include <algorithm>

// Function:    SORTVECTOR
// Purpose:     Sort a vector of numbers in ascending order

//{{XLP_SRC(SORTVECTOR)
    // NOTE - the FunctionWizard will add and remove mapping code here.
    //    DO NOT EDIT what you see in these blocks of generated code!
IMPLEMENT_XLLFN2(SORTVECTOR, "RP", "SORTVECTOR", "Vector", 
    "Lookup & Reference", "Sort a vector of numbers in ascending"
    " order", "Unsorted vector of numbers", "B()Vector Unsorted"
    " vector of numbers\0", 1)

extern "C" __declspec( dllexport )
LPXLOPER SORTVECTOR(const COper* Vector)
{
    CXlOper xloResult;
    BOOL bOk = TRUE;
    std::vector<double> vecVector;
    bOk = bOk && Vector->ReadVector(vecVector, "Vector", xloResult);
    if (!bOk)
        return xloResult.Ret();
//}}XLP_SRC

    // Use STL algorithm to sort input                         
    std::sort(vecVector.begin(), vecVector.end());             
    // Copy the sorted vector to xloResult                     
    xloResult = vecVector;                                     

    return xloResult.Ret();
}

Yet another overload of the CXlOper assignment operator is used to copy the STL vector into xloResult:

    xloResult = vecVector;                                     

The operator creates an array of values within xloResult and populates it with the values in vecVector.

This is one of the most pleasingly simple add-in functions in this tutorial. It is not necessarily very useful - a better implementation would sort a 2-dimensional array by a given column or columns, in ascending and descending order - but it is very short, and does a surprising amount of work in its two lines of hand-written code.

Next: Array formulae in Excel >>