3.3. The Variant
VBA contains a special data type, the Variant. Internally, the Variant is highly complex, but it's also extremely easy to use. The Variant is the default data type of VBA, so the following code casts myVar as a variant:
Dim myVar
The Variant data type allows you to use a variable with any of the intrinsic VBA data types, automatically working out what is the closest data type to the value you are assigning. When you consider the amount of processing required to determine what data type should be used for an abstract value, it's a testament to the VB development team at Microsoft that the Variant is as quick as it is. However, there is a slight performance hit when using both variant data and functions that return variant data, which we discuss later in this chapter.
Another drawback to using variant data is that your code becomes at best horrible to read, and at worst unreadable! To illustrate, consider two versions of the same function, the first written exclusively with variants, the second using strong typing:
Private Function GoodStuff(vAnything, vSomething, _
vSomethingElse)
If vAnything > 1 And vSomething > "" Then
GoodStuff = vAnything * vSomethingElse
Else
GoodStuff = vAnything + 10
End If
End Function
Private Function GoodStuff(iAnything As Integer, _
sSomething As String, _
iSomethingElse As Integer) _
As Integer
If iAnything > 1 And sSomething > "" Then
GoodStuff = iAnything * iSomethingElse
Else
GoodStuff = iAnything + 10
End If
End FunctionI know which one I'd rather maintain!
So how do you use variant data? Well, at the simplest level, you can ignore the fact that there are such things as data types (or, to be more precise, when using variants, you can ignore data subtypes). But to be a proficient VB programmer, if you use variants at all, it's best to be aware that every item of variant data has a subtype (like Integer, Long, or String) that corresponds to one of the major data types. And Decimal data is something of an exception: it's only available as a subtype of the Variant data type.
3.3.1. Special Variant Data Subtypes
In addition to all the intrinsic data types detailed above, the variant also supports the following special data types:
Empty
The Empty subtype is automatically assigned to new Variant variables when you declare them, but before you explicitly assign a value to them. For instance, in the code fragment:
Dim var1, var2 var2 = 0
the subtype of var1 is Empty, whereas var2 is only Empty for the brief period of time between the execution of the Dim statement on the first line and the assignment statement on the second line. In addition, a variable's subtype is Empty if it has been explicitly assigned a value of Empty, as in the following code fragment:
Dim var1 var1 = Empty
Null
Null is a special data subtype that indicates a variable doesn't contain any valid data. Usually, a variable is assigned a null value to indicate that an error condition exists. In order for its subtype to be Null, a variable must have a Null value assigned to it explicitly, as in the following line of code:
var1 = Null
A Null value also results from any operation in which the value of one or more of the expressions is Null, as the following code fragment shows:
dim myVarOne, myVarTwo, myVarThree 'All three variables are EMPTY now myVarOne = 9 myVarTwo=NULL 'We've made this variable NULL myVarThree = myVarOne + myVarTwo 'The result is NULL
Error
The Error subtype is used to store an error number. Error numbers are generated automatically by VBA, and can then be used by your error handling routine. Error-handling routines are discussed in Chapter 6.
3.3.2. Determining the Variant Subtype
Having the variant data type take care of your data typing is all well and good, but what happens when you need to know exactly what type of data is stored to a variable? VBA provides two functions: VarType, which returns a number that indicates the type of data stored to a variable; and TypeName, which returns a string containing the name of the data type.
VarType
The syntax of VarType is:
VarType(variablename)
where variablename is the name of the variable whose subtype you want to determine. You can provide the name of only a single variable at a time. The following table details the possible values returned by VarType and the data subtypes they represent. For purposes of reference, the table also lists the VBA constants you can use in your code to compare with the values returned by the VarType function.
Value Data Subtype VBA Constant 0 Empty vbEmpty
1 Null vbNull
2 Integer vbInteger
3 Long Integer vbLong
4 Single vbSingle
5 Double vbDouble
6 Currency vbCurrency
7 Date vbDate
8 String vbString
9 OLE Automation Object vbObject
10 Error vbError
11 Boolean vbBoolean
12 Array of Variant vbVariant
13 Data access object vbDataObject
14 Decimal vbDecimal
17 Byte vbByte
36 User-Defined Type vbUserDefinedType
8192 Array vbArray
Actually, the VarType function never returns 8192, as shown in the table; this is only a base figure indicating the presence of an array. When passed an array, VarType returns 8192 plus the value of the array's underlying data type. For example, if you pass the variable name of an array of string to VarType, the return value is 8200 (8192 + 8).
TypeName
The TypeName function allows you to write more readable, self-documenting code by returning the name of the data subtype rather than a more abstract number. The syntax for TypeName is:
result = TypeName(variable)
Like the VarType function, TypeName is read-only; you can use it to determine the subtype of a variable, but you can't use it to explicitly set the type of a variable. To do this, you must use the conversion functions discussed in the previous section. The following table shows the string that the TypeName function returns for each data subtype.
Return Value Data Subtype <object type> Actual type name of an object Boolean Boolean value: True or False Byte Byte value Currency Currency value Date Date or time value Decimal Decimal (single-precision) value Double Double-precision floating-point value Empty Uninitialized Error Error Integer Integer value Long Long integer value Nothing Object variable that doesn't yet refer to an object instance Null No valid data Object Generic object Single Single-precision floating-point value String Character string value Variant( ) Variant array Unknown Unknown object type
If you pass an array of a particular data type to TypeName, the same return string is used for the underlying data type of the array, suffixed with "( )" to denote an array. Therefore if you pass the variable name of an array of strings to TypeName, the return value is "String( )".
As for making your code more readable and easier to maintain, just look at this snippet:
If TypeName(x) = "Double" Then
Now you've no excuse for getting those nasty "type mismatch" errors!
3.3.3. Variant and Strongly Typed Data
The Variant might appear to be the answer to all your data type needs, but there's a price to pay. The variant is more than a data type, it's a program within itself. It takes a lot of processing to determine the data type of an abstract value. In tests I've carried out, an expression consisting of only variant data executes about 30% slower than the same expression using the correct intrinsic data types.
3.3.4. Variant and Strongly Typed Functions
The VBA language includes a number of string-handling functions that have two versions, one that returns a variant and the other that returns a string. The latter are suffixed with the old string-specifying character $ (for example, Left$), while the former simply include the name of the function (for example, Left).
I have put the two versions through some performance testing to determine if there is a significant difference in their performance. To simplify matters, I tested both Left and Left$ and Mid and Mid$. When using the strongly typed versions, I assigned the result to a string data type and passed string data types as parameters; when using the variant versions, I assigned the result to a variant and passed variant data types as parameters.
For example, here is a variant version of a sample code fragment that illustrates the performance tests:
Dim sString Dim sPartString sString = "ABCDEFGH" sPartString = Mid(sString, 1, 2)
and here is the String version:
Dim sString As String Dim sPartString As String sString = "ABCDEFGH" sPartString = Mid$(sString, 1, 2)
I found that the variant version of each function executed about 50% slower than its string counterpart. This obviously is a significant difference, and it suggests that we should use the typed versions of all functions whenever they are available.