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 .. 27 28 29 30 31 32 < 33 > 34 35 36 37 38 39 .. 168 >> Next

Important note
VB aligns the elements of structures along 4-byte boundaries but the default for VC 6.0 and VC .NET is to align to an 8-byte boundary. To avoid run-time errors or what would look like corruption of data you need to use a #pragma pack(4) statement where the C structure is defined (the recommended approach), or change the project settings default using a “/Zp4” compiler command line flag.
Here are some examples of good and bad user-type definitions:
Table 3.9 VB user type and C typedef examples
VB C Comments
Type VB_User_Type i as Integer d as Double s as String End Type #pragma pack(4) typedef struct { short iVal; double dVal; BSTR bstr; } C_u s e r _t yp e ; // restore default #pragma pack() GOOD. Note the different names of the structure and the variables contained within it. Note also the #pragma pack (4 ) which is required in order to prevent run-time errors.
Type User_Type i as Integer d as Double s as String End Type typedef struct { short iVal; double dVal; BSTR bstr; } C_u s e r _t yp e ; BAD Missing #pragma pack (4 ) will cause the double and the string to be misaligned and cause a run-time error.
Type User Type i as Integer End Type #pragma pack(4) typedef struct { int i ; } C_U S e r _t yp e ; #pragma pack() BAD C/C++ int is a 32-bit variable. VB’s Integer is 16-bit.
Using VBA 63
Table 3.9 (continued)
Type User_Type #pragma pack(4) BAD
i as Integer d as Double typedef struct { double d; Corresponding variables must
End Type be in the same order.
short i; } C_user_type;

#pragma pack()
User-defined types are best passed ByRef (the default) arriving at C/C++ as a pointer to the structure. Here is some example code, first the VB...
Type VB_User_Type i As Integer d As Double s As String End Type
Declare Function C_user_type_example Lib "example.dll" _
(Arg As VB_User_Type) As Integer
Function VB_USER_TYPE_TEST(i As Integer, d As Double, s As String) As Integer
Dim t As VB_User_Type
t.i = i t.d = d t.s = s
VB_USER_TYPE_TEST = C_user_type_example(t)
End Function
... and the corresponding C/C++ code:
#pragma pack(4) // required to be consistent with VB
typedef struct {
short iVal; double dVal;
BSTR bstr;
#pragma pack() // restore the default
short stdcall C_user_type_example(C_user_type *arg)
short retval;
Excel Add-in Development in C/C++
if(arg == NULL) return 0;
retval = arg->iVal;
retval += (short)(arg->dVal);
retval += SysStringByteLen(arg->bstr);
return retval;
This example code simply returns the sum of the integer argument, the integer part of the floating-point argument and, if it has been initialised, the byte-length of the BSTR.
3.6.13 VB object data type
VB objects are passed from VB to DLLs as dispatch pointers for use with the OLE 2 IDispatch interface. For example, range arguments passed to VB functions declared as taking Variants are of this type. If passed directly to DLL functions also declared as taking Variants, the DLL will have to understand the IDispatch interface in order to access the cell values. This can be avoided by converting ranges to array Variants as demonstrated in the example in section 3.6.11 above, and is discussed more in section 3.7 Excel ranges, VB arrays, SafeArrays, array Variants on page 64.
The OLE/COM IDispatch interface enables programs (known as OLE Automation Controllers ) to access the objects of other applications. Although this is relevant to the general subject of writing add-ins for Excel, the scope of this book does not cover these topics and all the mechanisms that these things entail. The Microsoft Excel 97 Developer’s Kit contains a chapter on doing just this as well as there being numerous other texts, and online help on MSDN.
The usefulness of arrays, especially in passing blocks of data between Excel, VB and C/C++ (in both directions) makes them an important topic. There are a number of different ways in which each of Excel, VB and C/C++ treat arrays. This can lead to some confusion and complexity. This section aims to reduce this by providing an overview of the different ways arrays can be created and represented, and to recommend an approach that removes much of the complexity.
Firstly, it is helpful to simply list all of the various array types:
• Excel literal worksheet array: can contain all of the basic worksheet data types. (See section 2.4 Worksheet data types and limits on page 10 for more information.)
• Excel range reference: an Excel object that refers to a collection of cells, whose values can intuitively be thought of as matrices or vectors, although, strictly speaking, not really an array.
Using VBA 65
• VB array: OLE SafeArray type, used to represent an array whose elements are all of the same type, determined at declaration. Supports all the basic data types and Variants.
• VB array Variant: An OLE Variant that contains an array; not to be confused with an array of Variants. The array contained is of type SafeArray. Its elements can be of any type including Variants.
Previous << 1 .. 27 28 29 30 31 32 < 33 > 34 35 36 37 38 39 .. 168 >> Next