Fundamental Variable Data Types

When you declare a variable, you can also supply a data type for the variable. All variables have a data type that determines what kind of data they can store. By default, if you don't supply a data type (or if you declare the variable implicitly), the variable is given the Variant data type.

The Variant Data Type

The Variant data type can store many kinds of data. Like a text box control on a form, a Variant variable is equally capable of storing numbers, strings of text, dates and times, or the Null value. You don't have to convert between these types of data when assigning them to a Variant variable; Visual Basic automatically performs any necessary conversion, as shown in the following example:

Dim varX                     ' Variant by default.
varX = "17"                  ' varX contains the two-character string "17".
varX = varX - 15             ' varX now contains the numeric value 2.
varX = "U" & varX            ' varX now contains the string "U2".

Although you can perform operations on Variant variables without much concern for what kind of data they actually contain, there are some pitfalls you'll want to avoid:

 If you perform arithmetic operations on a Variant, or use a Variant in an arithmetic function, the variable must contain a valid number. For example, you can't 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 can't perform any calculations on the value "1040EZ". However, you can perform numeric calculations on the values "+10" and "-1.7E62" because they are valid numbers.

 You can use the IsNumeric function to determine if the value contained by a Variant variable can be used as a valid number in an expression. For example:

If IsNumeric(varX) And IsNumeric(varY) Then
   varZ = varX * varY
Else
   varZ = Null
End If

 If you're concatenating strings, use the & operator instead of the + operator. The result of the + operator can be ambiguous when used with two Variant values.

If both of the Variant values contain numbers, then the + operator performs addition. If both of the Variant values contain strings, then the + operator performs string concatenation. However, if one of the values is a number and the other is a string, the situation becomes more complicated. Visual Basic first attempts to convert the string into a number. If the conversion is successful, then 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 Test()
   Dim varX As Variant, varY As Variant

   varX = "6"
   varY = "7"
   Debug.Print varX + varY, varX & varY
   varX = 6
   Debug.Print varX + varY, varX & varY
End Sub

produces the following result in the Debug window:

67      67
13      67

Important   When typing your code, it's important to 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 & as the type-declaration character for the variable name.

In addition to strings and numbers, Variant variables can also contain date/time values. For example:

Function Century() As Integer
   Dim varToday As Variant
   varToday = Now
   If varToday >= #1/1/2001# Then
      Century = 21
   Else
      Century = 20
   End If
End Function

In the same way that you can use the IsNumeric function to determine if a Variant variable contains a valid numeric value, you can use the IsDate function to determine if a Variant variable contains a valid date/time value. For example:

Function Century (ByVal varDate As Variant) As Variant
   If IsDate(varDate) Then
      Century = ((Year(varDate) - 1) \ 100) + 1
   Else
      Century = Null
   End If
End Function

Objects, including Automation objects, can also be stored in Variant variables. This can be useful when you need to 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.

See Also   For more information on arrays, see "Arrays" later in this chapter.

The Empty Value

Sometimes you need to know if a Variant variable has ever been assigned a value since the variable was created. A Variant variable has the Empty value before it's assigned a value. The Empty value is a special value different from 0, a zero-length string (""), or the Null value. You can use the IsEmpty function to determine if a Variant variable has the Empty value.

If IsEmpty(varX) Then varX = 0

When you use a Variant in an expression, an Empty value is treated as either 0 or a zero-length string, depending on the expression. The Empty value disappears as soon as any value is assigned to a Variant variable (including the value of 0, the zero-length string, and the Null value). You can set a Variant variable back to the Empty value by assigning the Empty keyword to the Variant.

The Null Value

The Variant data type can contain one other special value: Null. Null is commonly used in database applications to indicate unknown or missing data. Fields and controls that haven't been initialized have a default value of Null. You can use the IsNull function to determine if a Variant variable contains the Null value.

If IsNull(varX) And IsNull(varY) Then
   varZ = Null
Else
   varZ = 0
End If

A Null value has some unique characteristics:

 Expressions involving Null always result in Null. Thus, Null is said to propagate through expressions; if any part of the expression evaluates to Null, the entire expression evaluates to Null.

 Most functions return Null if you pass them Null, a Variant containing Null, or an expression that evaluates to Null as an argument.

 Null values propagate through intrinsic (built-in) functions that return Variant data types.

You can assign a Null value by using the Null keyword. For example:

varZ = Null

Only Variant variables can contain Null values. If you assign Null to a variable of any data type other than Variant, a trappable error occurs. Assigning Null to a Variant variable doesn't cause an error, and Null will propagate through expressions involving Variant variables (though Null doesn't propagate through certain functions). For example, the following code:

Sub Test()
   Dim varX As Variant, varY As Variant
   varX = "6"
   varY = Null
   Debug.Print varX + varY, varX & varY
End Sub

produces this result in the Debug window:

Null   6

In addition, you can return Null from any Function procedure that has a Variant return value.

Tip   The fact that Null propagates makes it useful as an error value. If you write Function procedures that return Null when an error occurs, and then combine these functions in expressions, you can use the IsNull function to test the final result of the expression to see if an error has occurred. Because Null propagates, the final result is Null if an error has occurred in any of the functions; you don't have to test the result of each function separately.

Other Fundamental Data Types

The Variant data type handles all types of fundamental data and converts between them automatically. However, you can usually create more concise, faster code by using other data types where appropriate. For example, if a variable will always contain small integer values, you can save several bytes, and significantly increase the speed of arithmetic operations on the variable, by declaring that variable to be Integer instead of Variant.

The following table lists the fundamental data types in Visual Basic,
including Variant.

See Also   You can also declare arrays of any of these fundamental data types. For more information, see "Arrays" later in this chapter.

When you declare a variable by using a Dim, Public, Private, or Static statement, you use the As type clause to specify the data type of the variable. For example, the following statements declare Integer, Currency, Double, and String variables, respectively:

Dim intX As Integer
Public curBillsPaid As Currency
Private dblAmt As Double
Static strName As String

See Also   For more information on the Dim, Public, Private, or Static statements, search the Help index for the name of the statement.

A declaration statement can combine multiple declarations, as in the following statements:

Dim intX As Integer, dblAmt As Double
Dim strName As String, curBillsPaid As Currency
Dim varTest, intY As Integer, varAmount

Important   In a multiple declaration statement, you must use the As type clause for each variable whose data type you want to specify. If you don't specify a data type, Visual Basic declares the variable as Variant. In the last line of code in the preceding example, Visual Basic declares only the variable intY as Integer. The variables Test and Amount are each declared as Variant.

Most of the Visual Basic data types match the data types for fields that contain data. The few field data types that aren't directly matched by a Visual Basic data type can be handled by another Visual Basic data type.

Note   SQL data types are also used in Microsoft Access queries. For information on these data types, search the Help index for "ANSI SQL data types."

If a variable must be able to accept Null values, declare it as a Variant rather than one of the other fundamental data types. The Variant data type can accept Null values, while the other fundamental data types cannot.

Numeric Data Types

If you know that a variable always stores whole numbers (for example, 12) rather than fractional numbers (for example, 3.57), declare it as an Integer or Long data type. Operations are faster with integers, and Integer and Long use less memory than Variant, Double, or Currency. Integers are especially useful as the counter variables in For...Next loops.

If the variable contains a fraction, declare it as a Single, Double, or Currency variable. The Currency data type supports up to 4 digits to the right of the decimal point and 15 to the left; it's a fast and accurate fixed-point data type suitable for monetary calculations. Floating-point (Single and Double) numbers have much larger ranges than Currency, but are subject to small rounding errors.

Note   Floating-point values can be expressed as mmmEeee, in which mmm is the mantissa and eee is the exponent (a power of ten). The highest positive value of a Single data type is 3.402823E+38, or 3.4 times 10 to the 38th power; the highest positive value of a Double data type is 1.79769313486231E+308, or about 1.8 times 10 to the 308th power.

If the variable contains binary data, declare it as an array of the Byte data type. Using Byte variables to store binary data preserves the data during format conversions. When String variables are converted between ANSI/DBCS and Unicode™ formats, any binary data in the variable may be corrupted. Visual Basic will automatically convert between ANSI/DBCS and Unicode in any of the following circumstances:

 Reading from files

 Writing to files

 Calling DLLs

All arithmetic operators work with the Byte data type.

All numeric variables can be assigned to each other and to variables of the Variant data type. Visual Basic rounds off (doesn't truncate) the fractional part of a floating-point number before assigning it to an integer.

The String Data Type

If you have a variable that will always contain a string and never a numeric value, you can declare it as a String data type.

Dim strAny As String

You can then assign strings to this variable and manipulate it by using string functions.

strAny = "Database"
strAny = Left(strAny, 4)

Fixed-length strings in standard modules can be declared as Public or Private. In form and report modules, fixed-length strings must be declared as Private.

By default, a string variable or argument is a variable-length string, which means the string grows or shrinks as you assign new data to it. You can also declare strings that have a fixed length. You specify a fixed-length string with the following syntax:

String * size

For example, the following code declares a string that is always 50 characters long:

Dim strEmpName As String * 50

If you assign a string of fewer then 50 characters, strEmpName is padded with enough trailing spaces to total 50 characters. If you assign a string that is too long for the fixed-length string, the extra characters are truncated. For example, the following code:

Dim strJust4 As String * 4
Dim strAny As String
strAny = "Database"
Debug.Print strAny
strJust4 = strAny
Debug.Print strJust4

produces the following result in the Debug window:

Database
Data

Because fixed-length strings are padded with trailing spaces, you may find the Trim, LTrim, and RTrim functions useful when working with them.

See Also   For more information on the Trim, LTrim, or RTrim functions, search the Help index for the name of the function.

Visual Basic compares strings in one of several different ways, depending on the Option Compare statement specified in the Declarations section of your modules. You can specify either Option Compare Database, Option Compare Binary, or Option Compare Text to determine the relative ordering used in a comparison, and whether or not string comparisons are case-sensitive.

Microsoft Access automatically inserts an Option Compare Database statement in the Declarations section of a new module, specifying that the string comparisons in that module are based on the database sort order. If no Option Compare statement is specified in a module, Visual Basic does case-sensitive Binary comparisons in that module based on the character's relative order of appearance in the ANSI character set.

See Also   For more information on the Option Compare statement, search the Help index for "Option Compare statement."

The Boolean Data Type

If you have a variable that will contain simple yes/no or on/off information, you can declare it as a Boolean data type. The default value of Boolean is False. In the following example, blnCreditExceeded is a Boolean variable that stores a simple True or False setting.

Dim blnCreditExceeded As Boolean
' Add all charges.
Do Until rstCharges.EOF
   curAmt = curAmt + rstCharges("Amount").Value
   rstCharges.MoveNext
Loop
' Ask if the credit limit is exceeded.
If curAmt > curLimit Then blnCreditExceeded = True

The Date Data Type

Date and time values can be contained both in the specific Date data type and in Variant variables. The same general characteristics apply to dates in both types.

See Also   For more information, see "The Variant Data Type" earlier in this chapter.

When other numeric data types are converted to Date, values to the left of the decimal represent date information, while values to the right of the decimal represent time. Midnight is 0, and noon is 0.5. Negative whole numbers represent dates before December 30, 1899.

The Object Data Type

Object variables are stored as 32-bit (4-byte) addresses that refer to objects within an application or within some other application. A variable declared as Object can subsequently be assigned (by using the Set statement) to refer to any actual object recognized by the application. For example:

Const conFilePath As String = "C:\Program Files\Microsoft Office\Office\Samples\"
Dim objDb As Object
Set objDb = OpenDatabase(conFilePath & "Northwind.mdb")

When declaring object variables, instead of using a Variant data type or the generic Object data type, declare objects as they are listed in the Classes box in the Object Browser. Visual Basic can resolve references to the properties and methods of objects with specific types at compile time rather than at run time. This catches common errors sooner, and makes your code run faster.

Tip   You may want to think of the objects listed in the Classes box in the Object Browser as additional data types that are available to you. You can declare objects from other applications and other applications can declare objects from your applicationin the same way that you declare ordinary object data types in Visual Basic.

See Also   For more information on objects and the Object Browser, see Chapter 5, "Working with Objects and Collections."

Argument Data Types

The arguments for procedures you write have the Variant data type by default. However, you can declare other data types for arguments. For example, the following function accepts a String and an Integer:

Function Reverse (strAny As String, ByVal intChars As Integer) As String
' Reverses the first intChars characters in strAny.
   Dim strTemp As String, intCount As Integer
   If intChars > Len(strAny) Then intChars = Len(strAny)
   For intCount = intChars To 1 Step - 1
      strTemp = strTemp + Mid(strAny, intCount, 1)
   Next
   Reverse = strTemp + Right(strAny, Len(strAny) - intChars)
End Function

If you specify a data type for an argument, you must either:

 Pass a value of that type for the argument.

 Declare the argument by using the ByVal keyword to specify that the argument is passed by value rather than by reference. When you pass a variable by value, changes to the variable in the procedure don't affect its value in the calling procedure.

See Also   For more information on the ByVal keyword, search the Help index for "ByVal keyword."

For example, you can't pass a Variant by reference to a string argument. Thus, the following code produces an error:

Dim varTest As Variant
varTest = "Testing"
Debug.Print Reverse(varTest, 4)      ' Error: argument type mismatch.

One way to avoid this problem is to pass an expression, rather than a Variant, for an argument. Visual Basic then evaluates the expression and, if it can, passes it as the required data type. The simplest way to turn a variable into an expression is to enclose it in parentheses. For example:

Debug.Print Reverse((varTest), 4)   ' Makes the variable an expression.

However, the best way to ensure that arguments are passed correctly is to declare the arguments with the ByVal keyword, as illustrated by the second argument, ByVal intChars As Integer, in the preceding Reverse function example. Thus, you can pass a Variant as the second argument to the Reverse function. For example:

Dim strTest As String, varTest As Variant
strTest = "Testing"
varTest = "2"
Debug.Print Reverse(strTest, varTest)      ' Works!

When you pass a variable to a procedure by reference, the variable's value can be changed by that procedure. On the other hand, when you pass the variable by value, only a copy of the variable is passed to the procedure; therefore, if the procedure changes that value, the change affects only the copy and not the variable itself. This is important in the Reverse function; if the second argument isn't declared with the ByVal keyword, bugs could appear in the code. For example, suppose the second argument wasn't declared with the ByVal keyword, and you called it as follows:

Dim intTest As Integer, strTest As String
intTest = 10
strTest = "Testing"
Debug.Print Reverse(strTest, intTest)   ' Now intTest = 7 (length of strTest).

You don't usually expect a function to modify its arguments, as happens here. To avoid this kind of side effect in any procedures that modify their arguments, declare those arguments with the ByVal keyword.

Function Return Data Types

The value returned by a function has a data type. When you define the function, you can declare the data type of the value the function returns. For example, the Reverse function in the preceding section returns a String.

As with variables, Visual Basic can work more efficiently with functions if you explicitly declare a data type for the values they return. If you don't declare a data type, functions use the Variant data type. For example, if you don't set a return value for a function (by assigning a value to the name of the function), the function returns a Variant containing the Empty value.

If you declare the function to return a String, as in the Reverse function example, the function returns a zero-length string ("") if you don't assign a return value. If you declare the function to return a numeric data type, such as Integer or Double, it returns zero if you don't explicitly assign a return value.

© 1996 Microsoft Corporation. All rights reserved.