Download (direct link):
Using VBA 57
3.6.9 Variant data type
A Variant is a multi-type variable that can contain (or point to) a variety of different data types. It superficially makes all data types look the same enabling functions to be declared that take Variants as arguments or return them. Such functions can therefore process more than one, or even all, data types. In VB, it is the default data type for variables: the omission of the As Type data type specifier anywhere it might appear is equivalent to a declaration of As Variant.
It is good practice to declare all argument, return and variable types explicitly. The code is far more readable, errors in scope are also avoided and VB is not saddled with unnecessary type conversions. The Option Explicit statement at the top of a code module forces the programmer to do just this.
The OLE Variant is represented in VB by the Variant data type and in C/C++ by the
VARIANT structure. When passed ByVal to C/C++ a Variant arrives as a VARIANT. The C structure can be thought of as containing two key (top-level) components:
• a VARTYPE vt (defined as an unsigned short in <wtypes.h>) containing a
numeric code corresponding to the type of data the variant contains;
• a large union of all the data types (some of which are pointers) that the OLE Variant supports.
Here is a simple C/C++ example which, if exported from a DLL and declared in VB, would simply convert a VB Integer to a Variant of integer type:
VARIANT stdcall int_to_variant(short val)
// Good practice to initialise the variant structure first VariantInit (&v);
// This VARTYPE specifies a 2-byte signed integer (i.e. a short), // equivalent to a VB Integer v.vt = VT_I2;
// Assign the passed-in value to the 'short' union member v.iVal = val;
Variants are important in the context of this book insofar as they play an important role in the simplest way of passing of arrays of data from worksheet ranges to C/C++ DLLs via VB. (There are ways to do this that don’t involve Variants.) They are also used to return variable-sized arrays of data from VB back to array formulae in the worksheet. (Use of Variants is the only way to do this.) The subject of passing arrays to and fro is covered in detail below in section 3.7 Excel ranges, VB arrays, SafeArrays, array Variants on page 64.
Variants are also useful in getting data from, and returning data to, cells in Excel where the type could be one of a number of things, say a string or a number.
The C API opens up some of Excel’s internal data storage structures, by-passing the need for Variants. These structures do, nevertheless, have much in common with Variants. (See Chapter 6 Passing Data between Excel and the DLL on page 105.)
Excel Add-in Development in C/C++
3.6.10 Variant types supported by VBA
Of the many data types supported by the OLE Variant, only the following are supported by VBA in Excel, and therefore only these need to be handled by a DLL function that is called from VBA.
Table 3.6 VBA - supported Variant types
Data type VARTYPE Numeric value С union member
Empty VT_EMPTY 0 (No associated data)
Long signed 32-bit integer VT_I4 2 long lVal
Short signed 16-bit integer VT_I2 3 short iVal
4-byte single-precision VT_R4 4 float fltVal
8-byte double-precision VT_R8 5 double dblVal
Currency VT_CY 6 CY *pcyVal
Date VT_DATE 7 DATE date (DATE is defined as double)
String VT_BSTR 8 BSTR bstrVal
Object VT_DIS PATCH 9 IDispatch *pdispVal (See VB Object type below)
Error VT_ERROR 10 ULONG ulVal (Easier to use than SCODE)
Boolean VT_BOOL 11 short boolVal
Variant (see notes below) VT_VARIANT 1 * 12 VARIANT *pvarVal or SAFEARRAY *parray
ByRef (see notes below) VT_BYREF 1 * 16384 0x4000 Pointer to one of the above data types
Array (see notes below) VT_ARRAY 1 * 8192 0x2000 SAFEARRAY *parray
Array and By Ref note
The VT_ARRAY and VT_BYREF bits are bit-wise or’d with the value of the associated data type. In a Variant array, therefore, the data type not only says that the Variant is an array but also what is the data type of the elements. If the Variant’s data type is bit-wise or’d with the VT_BYREF bit, then the Variant contains a pointer to the given data type.
Using VBA 59
If both bits are set, then the array that the Variant contains is an array of pointers to the given data type.
A Variant will only contain a Variant in conjunction with one or both of the VT_ARRAY and VT_BYREF bits. If the VT_BYREF bit is set then the pointer is accessed via the VARIANT *pvarVal data member. If it is the VT_ARRAY bit, then the Variant contains an array of Variants whose individual elements may be of mixed-type, and are accessed via the SAFEARRAY *parray data member. (See also note below.)
Array of Variants note
A Variant type of particular interest is a Variant containing an array of Variants. Such arrays are created when assigning a worksheet Range.Value property in VB to a Variant - one of the ways of passing an array originating in a range of worksheet cells to a C/C++ DLL. (See section 3.7 Excel ranges, VB arrays, SafeArrays, array Variants on page 64 for details.)