HOW TO: How do I truncate a numeric vector input at the first non-numeric cell?

Reference: Q0023

Article Last Modified on 28-Mar-2006

The information in this article applies to:

XLL+ for Visual Studio 2005 - 5.0
XLL+ for Visual Studio .NET - 4.2, 4.3.1, 5.0
XLL+ for Visual Studio 6 - 3, 4.1, 4.2, 4.3.1, 5.0

How do I truncate a numeric vector input at the first non-numeric cell?

Question

The XLL+ Function Wizard lets me specify that an input vector should be truncated at the first empty cell. Can I make it truncate the vector at the first non-numeric cell?

Answer

The XLL+ Function Wizard directly supports only a subset of the available vector flags (see vector<T> and matrix<T> Flags in the on-line help for a list of all flag values.

If you want to use non-standard flag values, you must write the code (4 lines of it) yourself, instead of letting the Function Wizard write it for you.

In the Function Wizard, set the type to be COper.
In your add-in function declare a local variable of type vector<T> to accept the input values.
Call the COper::ReadVector(...) method to read and validate the input.
If ReadVector fails (i.e. returns FALSE), return the error information which is placed into xloResult by ReadVector.

The example functions below demonstrate this technique for a vector<double> and a vector<int> argument.

Example

// Function:    DblArray
// Purpose:     Takes an array of doubles as an argument, and truncates 
//              from the first non-numeric cell

//{{XLP_SRC(DblArray)
    // NOTE - the FunctionWizard will add and remove mapping code here.
    //    DO NOT EDIT what you see in these blocks of generated code!
IMPLEMENT_XLLFN2(DblArray, "RP", "DblArray", "A", "User Defined",
    "Takes an array of doubles as an argument, and truncates"
    " from the first non-numeric cell", "Column of numbers\000",
    "\0appscope=1\0", 1)

extern "C" __declspec( dllexport )
LPXLOPER DblArray(const COper* A)
{
    XLL_FIX_STATE;
    CXlOper xloResult;
//}}XLP_SRC

    std::vector<double> vecA;
    long flags = XLA_ARRAY_FLAGS_STD | XLA_TRUNC_ONNONNUMERIC;
    if (!A->ReadVector(vecA, "A", xloResult, 0, (USHORT)-1, flags))
        return xloResult.Ret();
    for (size_t i = 0; i < vecA.size(); i++)
        vecA[i] *= 2.0;
    xloResult = vecA;
    return xloResult.Ret();
}

// Function:    IntArray
// Purpose:     Takes an array of integers as an argument

//{{XLP_SRC(IntArray)
    // NOTE - the FunctionWizard will add and remove mapping code here.
    //    DO NOT EDIT what you see in these blocks of generated code!
IMPLEMENT_XLLFN2(IntArray, "RP", "IntArray", "A", "User Defined",
    "Takes an array of integers as an argument", "Column of integ"
    "ers\000", "\0appscope=1\0", 1)

extern "C" __declspec( dllexport )
LPXLOPER IntArray(const COper* A)
{
    XLL_FIX_STATE;
    CXlOper xloResult;
//}}XLP_SRC

    std::vector<int> vecA;
    long flags = XLA_ARRAY_FLAGS_STD | XLA_TRUNC_ONNONNUMERIC;
    if (!A->ReadVector(vecA, "A", xloResult, 0, (USHORT)-1, flags))
        return xloResult.Ret();
    for (size_t i = 0; i < vecA.size(); i++)
        vecA[i] *= 2;
    xloResult.FromNumericVector(vecA);
    return xloResult.Ret();
}