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 application—in 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.