This chapter is from the book
This chapter is from the book
This chapter is from the book
Variable Data Types
The data type of a variable determines the kind of data the variable can hold. You specify a data type by including the As keyword in a Dim statement. Here is the general syntax:
Dim variableName As DataType
variableName is the name of the variable and DataType is one of the data types. Here's a rundown of the most useful VBA data types:
-
String—This type holds strings, which are simple text values. Here's a sample declaration and assignment statement (note the use of quotation marks in the assignment statement value; this tells VBA that the value is a string):
Dim newFileName As String newFileName = "Budget Notes.doc"
-
Date—This type holds date values, which refers to dates and/or times. Here are a few examples (note the use of the # character around the values; this tells VBA that the values are dates and/or times):
Dim myBirthDate As Date Dim myBirthTime As Date Dim anotherDate As Date myBirthDate = #8/23/59# myBirthTime = #3:02 AM# anotherDate = #4/27/07 16:05#
- Object—You use this type to hold generic objects, which I discuss in detail in Chapter 5, "Working with Objects."
- Byte—This rarely used type holds small, positive integer values (from 0 to 255).
-
Integer—This type holds integer values, which VBA defines as whole numbers between –32,768 and 32,767. Here's an example:
Dim paragraphNumber As Integer paragraphNumber = 1
-
Long—This type holds long integer values, which VBA defines as whole numbers between –2,147,483,648 and 2,147,483,647. Here's an example (note that you don't include commas—or periods, if you're in Europe—in numbers that would normally use one or more thousands separators):
Dim wordCount As Long wordCount = 100000
-
Boolean—This type holds Boolean values, which take one of two values: True or False. Here's an example:
Dim documentSaved As Boolean documentSaved = False
- Currency—This type holds monetary values. The value range is from –922,337,203,685,477.5808 to 922,337,203,685,477.5807.
-
Single—This type holds single-precision floating point values, which are numbers that have a decimal component. Here's an example:
Dim averageUnitSales As Single averageUnitSales = 50.3
-
Double—This type holds double-precision floating point values, which can accommodate much larger or smaller numbers than the Single type. Note, however, that the range available with the Single type should be more than enough for your VBA macros, so you'll probably never use the Double type. Here's an example:
Dim atomsInTheUniverse As Double atomsInTheUniverse = 2.0E+79
Here are a few notes to keep in mind when using data types:
- If you don't include a data type when declaring a variable, VBA assigns the Variant data type. This enables you to store any kind of data in the variable. However, this isn't a good idea because Variant variables use more memory and are much slower than the other data types. Therefore, always give your variables a specific data type. Note, however, that you may on occasion need a variable that can assume different data types. In that case, you should declare the variable using the Variant type.
|
→ |
For an example of a situation in which declaring a variable as a Variant is a good idea, see "Getting Input Using InputBox," p. 50. |
- If you declare a variable to be one data type and then try to store a value of a different data type in the variable, VBA often displays an error. For example, if you declare a variable using the Single type and you try to assign a value that's outside the Single type's allowable range, VBA displays an "Overflow" error message when you attempt to run the procedure.
- To specify the data type of a procedure argument, use the As keyword in the argument list. For example, the following Function statement declares variables Sales and Expenses to be Currency:
Function GrossMargin(Sales As Currency, Expenses As Currency)
- To specify the data type of the return value for a Function procedure, use the As keyword at the end of the Function statement:
Function GrossMargin(Sales, Expenses) As Single
Changing the Default Data Type
I mentioned in the preceding section that VBA assigns the Variant type to a variable if you don't specify a data type. However, VBA supports a number of DefType statements that let you redefine the default data type. These statements all use the following syntax:
DefType letter1[-letter2]
Here, Type is a three- or four-letter code that specifies the data type, and letter1 and letter2 define a range of letters. Note that this is a module-level statement, so you must place it at the top of a module, before any procedures or functions.
The idea is that any variable (or function argument or function result) that begins with one of these letters will be assigned the specified data type by default. For example, the DefInt keyword is used to set the default data type to Integer. If you want VBA to assign, say, the Integer data type to any variables that begin with the letters X through Z, you would add the following statement at the module level:
DefInt X-Z
Table 3.1 lists the various DefType keywords and the data types they represent.
Table 3.1. VBA's DefType keywords.
|
DefType |
Data Type |
|
DefBool |
Boolean |
|
DefByte |
Byte |
|
DefInt |
Integer |
|
DefLng |
Long |
|
DefCur |
Currency |
|
DefSng |
Single |
|
DefDbl |
Double |
|
DefDate |
Date |
|
DefStr |
String |
|
DefObj |
Object |
|
DefVar |
Variant |
Creating User-Defined Data Types
VBA's built-in data types cover a lot of ground and should be sufficient to meet most of your needs. However, VBA also lets you set up user-defined data types. These are handy for storing similar types of data in a single structure. For example, suppose your program is working with car makes and models. In this case, you might need to work with values for the manufacturer, the model, the year the car was made, and the purchase price. One way to go about this would be to set up variables for each item of data, like so:
Dim carMake As String Dim carModel As String Dim yearMade As Integer Dim carPrice As Currency
This approach works, but what if you need to work with the data from multiple cars at once? You could set up new variables for each car, but that seems too inefficient. A better way is to define a "CarInfo" data type that holds all the required information. Here's how you would do it:
Type CarInfo
make As String
model As String
made As Integer
price As Currency
End Type
The Type keyword tells VBA that you're creating a user-defined data type. In this example, the new data type is named CarInfo. The statements between Type and End Type define the various elements within the new data type. Note that you need to place this definition at the module level; VBA doesn't let you define new data types within a procedure.
Now you use the data type as you would any other. For example, the following statement declares a new variable named myCar to be of type CarInfo:
Dim myCar As CarInfo
From here, you refer to the various elements within the data type by separating the variable name and the element name with a period (.), like so:
myCar.make = "Porsche" myCar.model = "911 Turbo" myCar.made = 2007 myCar.price = 122000