The Variant data type allows you to store most types of data. You don't have to convert between these data types when assigning them to a Variant variable; Visual Basic automatically performs any necessary conversion, as in the following example.
Private someValue ' Variant by default.
someValue = "17"
' someValue contains "17" (a two-character string).
someValue = someValue - 15
' someValue now contains the numeric value 2.
someValue = "U" & someValue
' someValue now contains "U2" (a two-character string).
Tip
A Variant variable isn't a variable with no data type; rather, it's a variable that can freely change its type to accommodate the data stored in the variable. An uninitialized Variant variable contains the special Empty value. As soon as you assign data to the Variant variable, the variable assumes the data type most appropriate for the assigned data.
The Variant data type maintains an internal representation of stored values. This internal representation corresponds to one of the non-variant data types discussed earlier in this chapter, and it determines how Visual Basic treats the stored value when performing comparisons and other operations.
When you store numbers in a Variant variable, Visual Basic uses the most compact representation possible. For example, if you store a small number without a decimal fraction, the variant uses an Integer representation for the value. If you then assign a larger number, Visual Basic uses a Long value. If the value is very large or has a fractional component, Visual Basic uses a Double value. (Visual Basic automatically uses the Double internal representation instead of the Single or Currency type to store a number with a fractional component in a variant.)
Sometimes you want to use a specific representation for a number. For example, you might want a variant to store a numeric value as Currency to avoid round-off errors in later calculations. Visual Basic provides several conversion functions that you can use to convert values into a specific type (see "Converting Data Types" later in this chapter). To convert a value to Currency, for example, you use the CCur function:
payPerWeek = CCur(hours * hourlyPay)
An error occurs if you attempt to perform a mathematical operation or function on a Variant variable that doesn't contain a number or something that can be interpreted as a number. For example, you cannot perform any arithmetic operations on the value "U2" even though it contains a numeric character, because the entire value isn't a valid number. Likewise, you cannot perform any calculations on the value "1040EZ." However, you can perform calculations on the values "+10" and " – 1.7E6" because they're valid numbers. For this reason, you'll often want to determine whether a variant contains a value that can be used as a number. The IsNumeric function performs this task, as shown in the following example.
Do
anyNumber = InputBox("Enter a number")
Loop Until IsNumeric(anyNumber)
MsgBox "The square root is: " & Sqr(anyNumber)
If you assign a Variant variable containing a number to a String variable, Visual Basic automatically converts the representation of the number to a string. If you want to explicitly convert a number to a string, use the CStr function. You can also use the Format function to convert a number to a string that includes formatting such as currency, thousands separator, and decimal separator symbols.
For more information, see "Format" in Help. For information about writing macros for applications that will be distributed in foreign markets, see Appendix A, "Writing Code for International Use."
Generally, storing and using strings in Variant variables poses few problems. However, the result of the + operator can be ambiguous when it's used with two Variant values. If both of the Variant variables contain numbers, the + operator performs addition. If both of the Variant variables contain strings, the + operator performs string concatenation.
If one of the values is represented as a number and the other is represented as a string, the situation becomes more complicated. Visual Basic first attempts to convert the string into a number. If the conversion is successful, the + operator adds the two values; if the conversion is unsuccessful, it generates a "Type mismatch" error message.
To make sure that concatenation occurs, regardless of the representation of the value in the variables, use the & operator, as shown in the following example.
Sub StringCat()
Dim x, y
x = "6"
y = "7"
With Worksheets(1)
.Range("a1") = x + y 'concatenates values
.Range("b1") = x & y 'concatenates values
x = 6
.Range("a2") = x + y 'adds values
.Range("b2") = x & y 'concatenates values
End With
End Sub
The preceding example produces the following result on the worksheet:
67 67 13 67
When you type your code, make sure that you leave a space between any variable name and the & operator. If you don't leave a space, Visual Basic assumes you intended to use the ampersand (&) as the type-declaration character for the variable name. For more information about type-declaration characters, see "type-declaration character" in Help.
Variant variables can also contain date/time values. Several functions return date/time values. For example, the Now function returns the current date and time.
In the same way that you can use the IsNumeric function to determine whether a Variant variable contains a value that can be considered a valid numeric value, you can use the IsDate function to determine whether a Variant variable contains a value that can be considered a valid date/time value. You can then use the CDate function to convert the value into a date/time value. For more information, see "The Date Data Type" earlier in this chapter.
Variant variables can contain objects. This can be useful when you need to gracefully handle a variety of data types, including objects. For example, all the elements in an array must have the same data type. Setting the data type of an array to Variant allows you to store objects along with other data types in an array.
If you create your own error values using the CVErr function and you want to store them in variables, those variables must be of the Variant type. After you've assigned error values to those Variant variables, the variables have the Error subtype (Error is a subtype because there is no explicit Error data type). In the following example, myError is a Variant variable of subtype Error.
Dim myError myError = CVErr(2010)
Even though myError is a Variant variable, if you use it as an argument to the TypeName function, TypeName returns "Error," not "Variant." Using the TypeName function in this situation tells you whether a variable contains a user-defined error. You can also use the IsError function to test for error values.
For more information about user-defined errors, see Chapter 7, "Handling Run-Time Errors," or see "CVErr" or "IsError" in Help.
A Variant variable contains the Empty value until it's assigned a value. The Empty value is a special value that's different from 0 (zero), a zero-length string (""), or the Null value. You can test for the Empty value using the IsEmpty function.
If IsEmpty(z) Then z = 0
When a Variant variable contains the Empty value, you can use it in expressions; it's treated as either 0 (zero) or a zero-length string (""), depending on the expression.
The Empty value in a Variant variable disappears as soon as any value is assigned to that variable (including the value of 0 (zero), the zero-length string, and the Null value). You can set a Variant variable back to the Empty value by assigning the keyword Empty to the Variant variable.
z = Empty
The Variant data type can contain one other special value: Null. Null is commonly used in database applications to indicate unknown or missing data. Because of the way it's used in databases, Null has some unique characteristics:
You can also assign Null with the Null keyword.
z = Null
You can use the IsNull function to test for whether a Variant variable contains Null.
If IsNull(x) And IsNull(y) Then
z = Null
Else
z = 0
End If
Note
Variables aren't set to Null unless you explicitly assign Null to them, so if you don't use Null in your application, you don't have to write code that tests for it and handles it.
For more information about using Null in expressions, see "Null" in Help.
Most of the time, you don't have to be concerned with what internal representation Visual Basic is using for a particular stored value; Visual Basic handles conversions automatically. If you want to know what representation Visual Basic is using, however, you can use the TypeName or VarType function. The TypeName function returns a string for the data type; the VarType function returns a numeric value.
For example, if you store values with decimal fractions in a Variant variable, Visual Basic always uses the Double internal representation. If you know that your application doesn't need the high degree of accuracy (and slower speed) that a Double value entails, you can speed up your calculations by converting the values to Single, or even to Currency.
If TypeName(x) = "Double" Then x = CSng(x) ' Convert to Single.
For information about converting data types, see the following section, "Converting Data Types." For more information about the TypeName and VarType functions, see "TypeName" and "VarType" in Help.