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

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

String note
When VB passes strings to C/C++ via a Variant argument of type VT_BSTR, the string is a string of unsigned shorts, i.e., UNICODE wide characters. Care must be taken to distinguish between this case and when VB passes a VB String, which is a BSTR interpreted as a byte-string. Different system functions are required to read and create each type of string. (See also section 3.6.6 VB/OLE Strings on page 52.) In the case of Variant strings, the functions SysStringLen() and SysAllocStringLen() should be used in place of SysStringByteLen() and SysAllocStringByte-Len() respectively. The wide-char string to byte-string system conversion functions MultiByteToWideChar() and WideCharToMultiByte(), and their C library analogues mbstowcs() and wcstombs(), are also useful. (See the Variant conversion routines in the example project source file xloper.cpp, and also section 3.7 below.)
3.6.11 Variant types that Excel can pass to VB functions
Within Excel, VB functions declared with Variant arguments will be passed an even more limited subset by Excel worksheet formulae, namely:
Table 3.7 Variant types passed to VBA from Excel worksheets
VARTYPE Arguments that will be passed as this type
VT_R8 All numbers, with the exception of those formatted as dates or in the currency format.
VT_BOOL Excel’s TRUE and FALSE values. NOTE: Excel converts TRUE and FALSE to the numbers 1 and 0 respectively, whereas the Variant stores these as - 1 and 0. Care should be taken where conversions are being made.
(continued overleaf )
60
Excel Add-in Development in C/C++
Table 3.7 (continued )
VARTYPE Arguments that will be passed as this type
VT_DATE Any number formatted in one of Excel’s date formats or date-time formats. (Numbers displayed with a time format are passed as VT_R8.)
VT_BSTR All strings. (See note in above section.)
VT_DISPATCH Ranges (single-cell and multi-cell).
VT_ARRAY | VT_VARIANT Literal arrays.
VT_CY Any number formatted in the currency format defined for the current regional settings.
VT_ERROR All Excel error values.
VT_EMPTY All empty cells or omitted arguments.
A VB function declared as follows will return the type of the Variant as a number, using the VB function VarType(), except that ranges are converted, rather than VarType returning VT_DISPATCH. Single cell ranges are converted to the data type of the cell’s value. Multi-cell ranges are converted to arrays of Variants, type VT_ARRAY | VT VARIANT.
Function VariantType(v As Variant) As Integer VariantType = VarType(v)
End Function
The following VB function will similarly convert the Range to a Variant before calling VarType().
Function VariantRangeType(r As Range) As Integer VariantRangeType = VarType(r)
End Function
In both of these cases, the function VarType() is passing back the type of the Range object’s Value property.
The following VB code, which declares and calls a simple DLL function that returns a Variant, does no such conversion of ranges references, and therefore would return the value 9 (VT_DISPATCH) for anything other than literal arguments. For example, a worksheet formula =VariantTypeC(A1) would return 9 regardless of the contents of cell A1.
Using VBA 61
Declare Function C_vt_type Lib "example.dll" (ByRef arg As Variant) As Integer
Function VariantTypeC(v As Variant) As Double
VariantTypeC = C_vt_type(v)
End Function
Where the intention of the DLL function is to operate on the value of the range passed in, it is therefore necessary to convert the Range to one or more values. The simplest way to achieve this is to detect that the passed-in Variant is a range and then convert it to an array Variant, like so:
Declare Function C_vt_fn Lib "example.dll" (ByRef arg As Variant) As Integer
Function VariantFn(v As Variant) As Double
If IsObject(v) Then
VariantFn = C_vt_fn(v.Value)
Else
VariantFn = C_vt_fn(v)
End If
End Function
It is then the task of the DLL code to determine if the passed-in Variant is a simple value or an array. Note that in the above case, single-cell references are converted to 1x1 arrays. (See section 3.7 Excel ranges, VB arrays, SafeArrays, array Variants on page 64 for more about arrays.)
Excel error values are most easily read from the ulVal property of the variant. The numerical value is equivalent to the 2,148,141,008 plus the error code used in the C API and defined in the header file xlcall3 2.h. Variants containing Excel error values can also be created in VB using the CVerr() VB function. Table 3.8 provides a comparison of the various representations.
Table 3.8 Excel error codes
Error Variant ulVal value C API value CVe r r C ) argument
#NULL! 2148141008 0 2000
#DIV/0! 2148141015 7 2007
#VALUE! 2148141023 15 2015
#REF! 2148141031 23 2023
#NAME? 2148141037 29 2029
#NUM! 2148141044 36 2036
#N/A 2148141050 42 2042
62
Excel Add-in Development in C/C++
3.6.12 User-defined data types in VB
In C, a user-defined type is defined with a simple typedef struct {...} name; statement block. A virtually identical construct exists in VB: Type name ... End Type. Care needs to be taken to ensure that the variables within the type definition blocks in C and VB are equivalent data types and in the same order. You don’t need to give the variables or the structure itself the same names in both languages - all that is passed is a pointer to a block of memory that needs to be interpreted in the same way in both places.
Previous << 1 .. 26 27 28 29 30 31 < 32 > 33 34 35 36 37 38 .. 168 >> Next