Excel Add-in Development in C/C++ Applications in Finance phần 3 pot

Using VBA 69

A VB interface function declared as taking a range argument, would not be able to receive

literal values from the worksheet. If this were not a problem, then the VB code might

look like this, given that there is no need to call IsObject().

Function VtFunction(r As Range) As Variant

VtFunction = C_vt_function(r.Value)

End Function

The following line would have resulted in a Variant of type VT_DISPATCH being passed

to the DLL function.

VtFunction = C_vt_function(r)

3.7.3 Converting array Variants to and from C/C++ types

Array Variants are Variants that contain an array. The array itself is an OLE data type

called the SafeArray, declared as SAFEARRAY in the Windows header files. An under￾standing of the internal workings of the SAFEARRAY is not necessary to bridge between

VB and C/C++. All that’s required is a knowledge of some of the functions used to

create them, obtain handles to their data, release data handles, find out their size (upper

and lower bounds), find out what data-type the array contains, and, finally, destroy them.

The key functions, all accessible in C/C++ via the header windows.h, are:

SafeArrayCreate()

SafeArrayDestroy()

SafeArrayAccessData()

SafeArrayUnaccessData()

SafeArrayGetDim()

SafeArrayGetElemsize()

SafeArrayGetLBound()

SafeArrayGetUBound()

SafeArrayGetElement()

SafeArrayPutElement()

To convert an array Variant, the C/C++ DLL code needs to do the following:

• Determine that the Variant is an array by testing its type for the VT_ARRAY bit.

• Determine the element type by masking the VT_ARRAY bit from its type.

• Determine the number of dimensions using the SafeArray cDims property or by using

the SafeArrayGetDim() function.

• Determine the size of the array using SafeArrayGetUBound() and SafeAr￾rayGetLBound() for each dimension.

• Convert each array element from the possible Variant types that could originate from

a worksheet cell to the desired data type(s).

70 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 struc￾tures (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

Commands Functions

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 event(...)

...

End Sub

Workbook/worksheet event action:

Sub ObjectName event(...)

...

End Sub

Function FunctionName(...)As

return type

...

FunctionName = rtn val

End Function

(continued overleaf )