Variant variables maintain an internal representation of the values that they store. This representation determines how Visual Basic treats these values when performing comparisons and other operations. When you assign a value to a Variant variable, Visual Basic uses the most compact representation that accurately records the value. Later operations may cause Visual Basic to change the representation it is using for a particular variable. (A Variant variable is not a variable with no type; rather, it is a variable that can freely change its type.) These internal representations correspond to the explicit data types discussed in "Data Types" earlier in this chapter.
Note A variant always takes up 16 bytes, no matter what you store in it. Objects, strings, and arrays are not physically stored in the Variant; in these cases, four bytes of the Variant are used to hold either an object reference, or a pointer to the string or array. The actual data are stored elsewhere.
Most of the time, you don't have to be concerned with what internal representation Visual Basic is using for a particular variable; Visual Basic handles conversions automatically. If you want to know what value Visual Basic is using, however, you can use the VarType function.
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 does not need the high accuracy (and slower speed) that a Double value supplies, you can speed your calculations by converting the values to Single, or even to Currency:
If VarType(X) = 5 Then X = CSng(X) ' Convert to Single.
With an array variable, the value of VarType is the sum of the array and data type return values. For example, this array contains Double values:
Private Sub Form_Click()
Dim dblSample(2) As Double
MsgBox VarType(dblSample)
End Sub
Future versions of Visual Basic may add additional Variant representations, so any code you write that makes decisions based on the return value of the VarType function should gracefully handle return values that are not currently defined.
For More Information For information about the VarType function, see "VarType Function" in the Language Reference. To read more about arrays, see "Arrays" later in this chapter. For details on converting data types, see "Data Types" earlier in this chapter.
When you store whole numbers in Variant variables, 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 will use a Long value or, if it is very large or has a fractional component, a Double value.
Sometimes you want to use a specific representation for a number. For example, you might want a Variant variable 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" earlier 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 that does not 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 is not a valid number. Likewise, you cannot perform any calculations on the value 1040EZ; however, you can perform calculations on the values +10 or -1.7E6 because they are valid numbers. For this reason, you often want to determine if a Variant variable contains a value that can be used as a number. The IsNumeric function performs this task:
Do
anyNumber = InputBox("Enter a number")
Loop Until IsNumeric(anyNumber)
MsgBox "The square root is: " & Sqr(anyNumber)
When Visual Basic converts a representation that is not numeric (such as a string containing a number) to a numeric value, it uses the Regional settings (specified in the Windows Control Panel) to interpret the thousands separator, decimal separator, and currency symbol.
Thus, if the country setting in the Windows Control Panel is set to United States, Canada, or Australia, these two statements would return true:
IsNumeric("$100")
IsNumeric("1,560.50")
While these two statements would return false:
IsNumeric("DM100")
IsNumeric("1.560,50")
However, the reverse would be the case — the first two would return false and the second two true — if the country setting in the Windows Control Panel was set to Germany.
If you assign a Variant containing a number to a string variable or property, Visual Basic converts the representation of the number to a string automatically. 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. The Format function automatically uses the appropriate symbols according to the Regional Settings Properties dialog box in the Windows Control Panel.
For More Information See "Format Function" and topics about the conversion functions in the Language Reference. For information on writing code for applications that will be distributed in foreign markets, see "International Issues."
Generally, storing and using strings in Variant variables poses few problems. As mentioned earlier, however, sometimes the result of the + operator can be ambiguous when used with two Variant values. If both of the Variants contain numbers, the + operator performs addition. If both of the Variants contain strings, then the + operator performs string concatenation. But 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 unsuccessful, it generates a Type mismatch error.
To make sure that concatenation occurs, regardless of the representation of the value in the variables, use the & operator. For example, the following code:
Sub Form_Click ()
Dim X, Y
X = "6"
Y = "7"
Print X + Y, X & Y
X = 6
Print X + Y, X & Y
End Sub
produces this result on the form:
67 67
13 67
Note Visual Basic stores strings internally as Unicode. For more information on Unicode, see "International Issues."
Variant variables can also contain date/time values. Several functions return date/time values. For example, DateSerial returns the number of days left in the year:
Private Sub Form_Click ()
Dim rightnow, daysleft, hoursleft, minutesleft
rightnow = Now ' Now returns the current date/time.
daysleft = Int(DateSerial(Year(rightnow) _
+ 1, 1, 1) - rightnow)
hoursleft = 24 - Hour(rightnow)
minutesleft = 60 - Minute(rightnow)
Print daysleft & " days left in the year."
Print hoursleft & " hours left in the day."
Print minutesleft & " minutes left in the hour."
End Sub
You can also perform math on date/time values. Adding or subtracting integers adds or subtracts days; adding or subtracting fractions adds or subtracts time. Therefore, adding 20 adds 20 days, while subtracting 1/24 subtracts one hour.
The range for dates stored in Variant variables is January 1, 0100, to December 31, 9999. Calculations on dates don't take into account the calendar revisions prior to the switch to the Gregorian calendar, however, so calculations producing date values earlier than the year in which the Gregorian calendar was adopted (1752 in Britain and its colonies at that time; earlier or later in other countries) will be incorrect.
You can use date/time literals in your code by enclosing them with the number sign (#), in the same way you enclose string literals with double quotation marks (""). For example, you can compare a Variant containing a date/time value with a literal date:
If SomeDate > #3/6/93# Then
Similarly, you can compare a date/time value with a complete date/time literal:
If SomeDate > #3/6/93 1:20pm# Then
If you do not include a time in a date/time literal, Visual Basic sets the time part of the value to midnight (the start of the day). If you do not include a date in a date/time literal, Visual Basic sets the date part of the value to December 30, 1899.
Visual Basic accepts a wide variety of date and time formats in literals. These are all valid date/time values:
SomeDate = #3-6-93 13:20#
SomeDate = #March 27, 1993 1:20am#
SomeDate = #Apr-2-93#
SomeDate = #4 April 1993#
For More Information For information on handling dates in international formats, see "International Issues."
In the same way that you can use the IsNumeric function to determine if a Variant variable contains a value that can be considered a valid numeric value, you can use the IsDate function to determine if a Variant 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 example, the following code tests the Text property of a text box with IsDate. If the property contains text that can be considered a valid date, Visual Basic converts the text into a date and computes the days left until the end of the year:
Dim SomeDate, daysleft
If IsDate(Text1.Text) Then
SomeDate = CDate(Text1.Text)
daysleft = DateSerial(Year(SomeDate) + _
1, 1, 1) - SomeDate
Text2.Text = daysleft & " days left in the year."
Else
MsgBox Text1.Text & " is not a valid date."
End If
For More Information For information about the various date/time functions, see "Date Function" in the Language Reference.
Objects can be stored in Variant variables. 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 alongside other data types in an array.