in black and white
Main menu
Share a book About us Home
Biology Business Chemistry Computers Culture Economics Fiction Games Guide History Management Mathematical Medicine Mental Fitnes Physics Psychology Scince Sport Technics

Excel add in development in C++ Aplications in finance - Dalton S.

Dalton S. Excel add in development in C++ Aplications in finance - Wiley publishing , 2005. - 425 p.
ISBN 0-470-02469-0
Download (direct link): exceladdindevelopmentincand2005.pdf
Previous << 1 .. 52 53 54 55 56 57 < 58 > 59 60 61 62 63 64 .. 168 >> Next

Conversion is also necessary when you want to declare a DLL function as being capable of returning different data types, for example, a string or a number. In this case the function needs to return a pointer to an xloper that is not on the stack, i.e., that will survive the return statement.
The following sections provide a more detailed discussion of the xloper types and give examples of how to convert them to C/C++ types or to create them from C/C++ types. Some of the examples are function methods from the cpp_xloper class.
The cpp_xloper relies on a set of routines for converting from one xloper type to another, as well as to and from native C/C++ types. Many of these routines are reproduced in the examples in section 6.8 below. Of particular importance is the Excel C API function xlCoerce. This function, accessed via the C API interface function Excel4(), attempts
Passing Data between Excel and the DLL
to return an xloper of a requested type from the type of the passed-in xloper. It is covered in detail in section 8.7.3 Converting one xloper type to another: xlCoerce on page 201. In the examples that follow, this function is itself wrapped in a function whose prototype is:
bool coerce_xloper(xloper *p_op, xloper &ret_val, int target_type);
This attempts to convert any xloper to an xloper of target_type. It returns false if unsuccessful and true if successful, with the converted value returned via the pass-by-ref argument, ret_val. The code for this function is listed in section 8.7.3 on page 201.
Chapter 3 Using VBA discusses the OLE Variant structure and the various types supported by VBA, as well as the more limited subset that Excel passes to VBA functions declared as taking Variant arguments. It is also useful to have a number of conversion routines in an XLL that you also wish to use as interface to VBA, or that you might want to use to access COM. The cpp_xloper class has a number of these:
cpp_xloper(VARIANT *pv); // Takes its type from the VARTYPE void operator=(VARIANT *); // Same type as passed-in Variant
bool AsVariant(VARIANT &var); // Return an equivalent Variant
The first two, a constructor and an overloaded assignment operator, rely on the following routine. (The code for the function array_vt_to_xloper() is a variation on this function. All the following code is listed in xloper.cpp in the example project on the CD ROM.)
#include <ole2.h>
#define VT_XL_ERR_OFFSET 2148141008ul
bool vt_to_xloper(xloper &op, VARIANT *pv, bool convert_array) {
if(pv->vt & (VT_VECTOR | VT_BYREF)) return false;
if(pv->vt & VT_ARRAY)
if(!convert_array) return false;
return array_vt_to_xloper(op, pv);
case VT_R8:
op.xltype = xltypeNum; op.val.num = pv->dblVal; break;
Excel Add-in Development in C/C++
case VT_I2:
op.xltype = xltypeInt; op.val.w = pv->iVal; break;
case VT_BOOL:
op.xltype = xltypeBool; op.val._bool = pv->boolVal; break;
case VT_ERROR:
op.xltype = xltypeErr;
op.val.err = (unsigned short)(pv->ulVal - VT_XL_ERR_OFFSET); break;
case VT_BSTR:
op.xltype = xltypeStr;
op.val.str = vt_bstr_to_xlstring(pv->bstrVal); break;
case VT_CY:
op.xltype = xltypeNum;
op.val.num = (double)(pv->cyVal.int64 / 1e4); break;
default: // type not converted return false;
return true;
The third converts in the other direction and relies on the following routine:
bool xloper_to_vt(xloper *p_op, VARIANT &var, bool convert_array) {
VariantInit(&var); // type is set to VT_EMPTY
case xltypeNum: var.vt = VT_R8; var.dblVal = p_op->val.num; break;
case xltypeInt:
var.vt = VT_I2; var.iVal = p_op->val.w; break;
case xltypeBool:
var.vt = VT_BOOL;
var.boolVal = p_op->val._bool;
case xltypeStr:
var.vt = VT_BSTR;
var.bstrVal = xlstring_to_vt_bstr(p_op->val.str); break;
Passing Data between Excel and the DLL
case xltypeErr:
var.vt = VT_ERROR;
var.ulVal = VT_XL_ERR_OFFSET + p_op->val.err; break;
case xltypeMulti: if(convert_array)
VARIANT temp_vt;
SAFEARRAYBOUND bound[2]; long elt_index[2];
bound[0].lLbound = bound[1].lLbound = 0; bound[0].cElements = p_op->val.array.rows; bound[1].cElements = p_op->val.array.columns;
var.vt = VT_ARRAY | VT_VARIANT; // array of Variants var.parray = SafeArrayCreate(VT_VARIANT, 2, bound);
if(!var.parray) return false;
xloper *p_op_temp = p_op->val.array.lparray;
for(WORD r = 0; r < p_op->val.array.rows; r++)
for(WORD c = 0; c < p_op->val.array.columns;)
// Don't convert array within array
xloper_to_vt(p_op_temp++, temp_vt, false);
elt_index[0] = r; elt_index[1] = c++;
SafeArrayPutElement(var.parray, elt_index, &temp_vt);
// else, fall through to default option
default: // type not converted return false;
return true;
It is important to note that Variant strings are wide-character OLE BSTRs, in contrast to the byte-string BSTRs that Excel VBA uses for its String type when exchanging data with Excel and with a DLL declared as taking a String (in VB)/BSTR (in C/C++) argument. The following code shows both conversions:
Previous << 1 .. 52 53 54 55 56 57 < 58 > 59 60 61 62 63 64 .. 168 >> Next