Download (direct link):
Excel Add-in Development in C/C++
To create an array Variant, the C/C++ DLL code needs to do the following:
• Call SafeArrayCreate(), having initialised an array of SAFEARRAYBOUND structures (one for each dimension), to obtain a pointer to the SafeArray.
• Initialise a VARIANT using VariantInit().
• Assign the element type bit-wise or’d with VT_ARRAY to the Variant type.
• Assign the SafeArray pointer to the Variant parray data member.
• Set the array element data (and sub-types, if Variants).
The final points in each set of steps above can be done element-by-element using SafeAr-rayGetElement() and SafeArrayPutElement(), or, more efficiently, by accessing the whole array in one memory block using SafeArrayAccessData() and SafeAr-rayUnaccessData(). When accessing the whole block in one go, it should be borne in mind that SafeArrays store their elements column-by-column, in contrast to Excel’s C API array types, the xl_array (see page 107) and the xltypeMulti xloper (see page 111), where the elements are stored row-by-row.
Array Variant arguments passed by reference can be modified in place, provided that the passed-in array is first released using SafeArrayDestroy() before being replaced with the array to be returned.
The cpp_xloper class converts Variants of any type to or from an equivalent xloper type. (See sections 6.2.3 The xloper structure on page 111, and 6.4 A C++ class wrapper for the xloper - cpp_xloper on page 121. See also the Variant conversion routines in the example project source file, xloper.cpp.) The following example code demonstrates this:
VARIANT stdcall C_vt_array_example(VARIANT *pv)
static VARIANT vt;
// Convert the passed-in Variant to an xloper within a cpp_xloper cpp_xloper Array(pv);
// Access the elements of the xloper array using the cpp_xloper // accessor functions...
// Convert the xloper back to a Variant and return it Array.AsVariant(vt); return vt;
Note on memory management
One advantage of passing Variant SafeArrays back to VB is that VB can safely delete the array and free its resources, and will do this automatically once it has finished with it. Equally, if a passed-in array parameter is used as the means to return an array, and an array is already assigned to it, the DLL must delete the array using SafeArrayDestroy() before creating and returning a new one. (The freeing of memory passed back to Excel directly from an XLL is a little more complex - see Chapter 7 Memory Management on page 161 for details.)
3.7.4 Passing VB arrays to and from C/C++
You may want to pass a VB array directly to or from a DLL function. When passing a VB array to a DLL, the C/C++ function should be declared in the VB module as shown in the following example. (The ByRef keyword is not required as it is the default.)
Using VBA 71
Declare Function C_safearray_example "example.dll" (ByRef arg() As Double) As Double
The corresponding C/C++ function would be prototyped as follows:
double stdcall C_SafeArray_Example(SAFEARRAY **pp_Arg);
As you can see, the parameter ByRef arg() is delivered as a pointer to a pointer to a SAFEARRAY. Therefore it must be de-referenced once in all calls to functions that take pointers to SAFEARRAYs as arguments, for example, the OLE SafeArray functions.
When returning VB arrays (i.e., SafeArrays) from the DLL to VB, the process is similar to that outlined in the previous sections for array Variants. SafeArray arguments passed by reference can also be modified in place, provided that the passed-in array is first released using SafeArrayDestroy().
In practice, once you have code that accepts and converts array Variants, it is simpler to first convert the VB array to array Variant. This is done by simple assignment of the array name to a Variant.
3.8 COMMANDS VERSUS FUNCTIONS IN VBA
Section 2.8 Commands versus functions in Excel on page 19 describes the differences between commands and functions within Excel. The differences between the parallel concepts of commands and functions in VBA are summarised in the Table 3.10.
Table 3.10 Commands versus functions in VBA
Purpose Code containing instructions to be executed in response to a user action or system event. Code intended to process arguments and/or return some useful information. May be worksheet functions or VB functions.
VB code (see also sections below) Macro command: Sub CommandName(...) End Sub Command object event: Sub CmdObjectName eventC..) End Sub Workbook/worksheet event action: Sub ObjectName eventC..) End Sub Function FunctionName(...)As returntype FunctionName = rtnval End Function
(continued overleaf )
Excel Add-in Development in C/C++
Table 3.10 (continued)
VB code location Macro command: • Worksheet code object • Workbook code object • VB module in workbook • VB module outside workbook Command object event: • Code object of command object container Worksheet object event: • Worksheet code object Workbook object event: • Workbook code object Worksheet function: • VB module in workbook • VB module outside workbook VB project function: • Worksheet code object • Workbook code object • VB module in workbook • VB module outside workbook