Passing an argument of Variant data type is very similar to passing any other argument type. In the DLL, you can use the VARIANT data structure to access the data contained in the argument. See Chapter 5 in Volume 2 of the OLE 2 Programmer's Reference for descriptions of the VARIANT data type.
The VARIANT type is a C-language structure containing a single member for the variable type, three reserved members, and a large named union that is used to access the variable data depending on the type.
For example, this C-language function determines the data type contained in the VARIANT argument passed by Visual Basic:
short WINAPI VariantExample(VARIANT vt)
{
if (vt.vt == VT_DISPATCH) // variant is an object
return -1;
else if (vt.vt == VT_BSTR) // variant is a string
return _wtoi(vt.bstrVal);
else if (vt.vt == VT_I2) // variant is an integer
return vt.iVal;
else // variant is something else
return -3;
}
This Visual Basic code declares and calls the VariantExample function:
Declare Function VariantExample Lib "debug\ADVDLL.DLL" _
(ByVal v As Variant) As Integer
Sub VariantArgTest()
MsgBox VariantExample(Worksheets(1)) ' -1
MsgBox VariantExample("25") ' 25
MsgBox VariantExample(5) ' 5
MsgBox VariantExample(3.2) ' -3
End Sub
You could use this information to implement a function that accepts either a Range object or a text description of a range. If the argument contains an object, you can use IDispatch to access properties and methods of the object directly. If the argument contains a string, you can use IDispatch to create an object and then access its properties and methods.
Visual Basic in Microsoft Excel does not support all the data types supported by the VARIANT structure. The following table shows the allowed data types and their value constants. Microsoft Excel never returns a variant with a data type not shown on this list.
Data Type |
Variant Constant | |
Boolean |
VT_BOOL | |
Currency (scaled integer) |
VT_CY | |
Date |
VT_DATE | |
Double (double-precision floating-point) |
VT_R8 | |
Integer |
VT_I2 | |
Long (long integer) |
VT_I4 | |
Object |
VT_DISPATCH | |
Single (single-precision floating-point) |
VT_R4 | |
String |
VT_BSTR |