Specifying the Data Type of a Variable

If you don't specify the variable's data type in the Dim statement, you end up with the untyped variable. Untyped variables in VBA are always Variant data types. It's highly recommended that you create typed variables. When you declare a variable of a certain data type, your VBA procedure runs faster because Visual Basic does not have to stop to analyze the Variant variable to determine its type.

Visual Basic can work with many types of numeric variables. Integer variables can only hold whole numbers from -32,768 to 32,767. Other types of numeric variables are Long, Single, Double, and Currency. Long variables can hold whole numbers in the range -2,147,483,648 to 2,147,483,647. As opposed to Integer and Long variables, Single and Double variables can hold decimals. String variables are used to refer to text. When you declare a variable of String data type, you can tell Visual Basic how long the string should be. For instance:

Dim extension As String * 3

declares the fixed-length String variable named extension that is three characters long.

If you don't assign a specific length, the String variable will be dynamic. This means that Visual Basic will make enough space in computer memory to handle whatever amount of text is assigned to it.

After you declare a variable, you can only store the type of information in it that you indicated in the declaration statement. Assigning string values to numeric variables or numeric values to string variables results in the error message "Type mismatch" or causes Visual Basic to modify the value.

For example, if your variable was declared to hold whole numbers and your data uses decimals, Visual Basic will disregard the decimals and use only the whole part of the number. Try out the MyNumber procedure, as shown below, to see how Visual Basic modifies the data to fit the variable data type:

Sub MyNumber()

Dim myNum As Integer myNum = 23.11 MsgBox myNum End Sub

If you don't declare a variable with a Dim statement, you can still designate a type for it by using a special character at the end of the variable name. To declare the FirstName variable as String, you can append the dollar sign to the variable name, as shown below:

Dim FirstName$

The above declaration is the same as Dim FirstName As String. Other type declaration characters are shown in Table 3-2. Table 3-2: Type declaration characters

Data Type Character

Integer %

Long &

Notice that the type declaration characters can only be used with six data types. To use the type declaration character, append the character to the end of the variable name.

The AgeCalc2 procedure demonstrates the use of the type declaration characters shown in Table 3-2:

Sub AgeCalc2()

'variable declaration Dim FullName$ DateOfBirth As Date Dim Age%

'assign values to variables FullName$ = "John Smith" DateOfBirth = #01/03/1967#

Single Double

Currency String

'calculate age

'print results to the Immediate window Debug.Print FullName$ & " is " & Age% & " years old." End Sub

0 0

Post a comment