Using Variants

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