Code generated by the wizard

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

        CXlOper* NORMSINV2_Impl(CXlOper& xloResult, double Probability)
{
    // End of generated code 
//}}XLP_SRC 
    // TODO - set the value of xloResult, or return another value 
    //          using CXlOper::RetXXX() or throw a CXlRuntimeException. 
    return xloResult.Ret();                                    
}

Note each of the changed pieces of code:

1. The function now returns CXlOper*.
2. An extra argument 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:

CXlOper* NORMSINV2_Impl(CXlOper& xloResult, double Probability)
{
//}}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 in which it is declared. 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: Error values in Excel >>