Inside CXlOper

Code generated by the wizard

As you return to DevStudio, you will see that the wizard has inserted the following source code at the end of your C++ file. The code that is interestingly different from our previous example, NORMSDIST2, is marked below.

// Function:    NORMSINV2
// Purpose:     Returns the inverse of the standard normal cumulative distribution. The distribution has a mean of zero and a standard deviation of one.

//{{XLP_SRC(NORMSINV2)
    // NOTE - the FunctionWizard will add and remove mapping code here.
    //    DO NOT EDIT what you see in these blocks of generated code!
IMPLEMENT_XLLFN2(NORMSINV2, "RB", "NORMSINV2", "Probability", 
    "Statistical", "Returns the inverse of the standard normal"
    " cumulative distribution. The distribution has a mean of"
    " zero and a standard deviation of one.", "Probability is a"
    " probability corresponding to the normal distribution, a"
    " number between 0 and 1 exclusive", "\0", 1)

extern "C" __declspec( dllexport )
LPXLOPER NORMSINV2(double Probability)               
{
    CXlOper xloResult;                               
//}}XLP_SRC

    // TODO - Set the value of xloResult             
    return xloResult.Ret();                          
}

Note each of the changed pieces of code:

1. The function now returns LPXLOPER. This type is a pointer to CXlOper.
2. A local variable xloResult is declared, of type CXlOper. This is used to hold the data (of variable type) that we will return to Excel.
3. The value xloResult.Ret() is returned by the function.

What is CXlOper ?

CXlOper is a C++ class, which performs many useful tasks when managing data that is to be shared with Excel. It represents a value of variable type. Among the types of value it can hold are:

• double
• boolean
• string
• error
• array
• reference to a range of cells

The CXlOper assigment operator, '=', is overloaded, which means that we can use code like the following to set its values:

CXlOper xloResult;
xloResult = 9.9;        // Set the type to double, and the value to 9.9
xloResult = "abc";      // Set the type to string, and the value to "abc"
xloResult = xlerrValue; // Set the type to error, and the value to #VALUE!

Other assignment operators allow you to set a CXlOper's value to an array.

The CXlOper class is used throughout XLL+ to hold values that are shared with Excel. If you request data from Excel, for example to get the current language settings, it will be returned in a CXlOper.

See CXlOper in the class reference for full details of CXlOper.

Adding some useful code

Add code to the function to call our stand-alone function, as shown below:

extern "C" __declspec( dllexport )
LPXLOPER NORMSINV2(double Probability)
{
    CXlOper xloResult;
//}}XLP_SRC

    double Z;                                
    if (InverseCumNormal(Probability, &Z))   
        xloResult = Z;                       
    else                                     
        xloResult = xlerrNum;                
    return xloResult.Ret();
}

Depending on whether InverseCumNormal() succeeds we will either set CXlOper to contain the result of the calculation, or set it to contain the error code xlerrNum.

xlerrNum appears in Excel as #NUM!. For a full list of error codes, see Error codes.

Why xloResult.Ret() ?

xloResult is a local variable, and therefore goes out of scope at the end of the function. This means that its destructor will be called, and its value will be destroyed, and will therefore not be available to Excel. The Ret() method deals with this problem. It carries out three steps:

1. Transfer the value in the CXlOper to a static buffer.
2. Mark the data as belonging to the XLL, so that Excel will ensure that a callback in the XLL is used to destroy it later.
3. Returns a pointer to the static buffer that now contains the data.

All this work is essential but dull, and is completely handled by the Ret() method, so that you need not concern yourself with it further.

Our second add-in function is now complete and we're ready to build and test it.

Next: Errors in Excel >>