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.
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 will probably never use the Double type. Here's an example:
```
Dim atomsInTheUniverse As Double
atomsInTheUniverse = 2.0E+79
```

Double values often use exponential notation, such as the value 2.0E+79 used in the Double example. A positive number, say X, after the E symbol means that you move the decimal point X positions to the right to get the actual number. So, for example, 2.0E+3 is the same thing as 2000.A negative number, say -X, after the E means that you move the decimal point X positions to the left. So 3.14E-4 is the equivalent of 0.000314.

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.
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

Tips

Many programmers remind themselves of each variable's data type by applying data type prefixes to the variable names. For example, the data type prefix for a String variable is str, so the declaration for such a variable might look like this:

Dim strName As String

This helps you avoid programming errors because you're less likely to try and store, say, an Integer value in a String variable if that variable's name begins with str.Here are some other common data type prefixes:

Data Type	Prefix
String	str or s
Date	dte or dtm
Object	obj
Byte	byt
Integer	int or i
Long	lng
Boolean	bln or b
Currency	cur
Single	sgl or sng
Double	dbl
Variant	vnt

[Previous] [Contents] [Next]

Variable Data Types

Tips

In this tutorial: