Data Types

When you declare or create a variable, it is best to think about what kind of data the variable will hold and then create a variable of the appropriate data type. You can think of a variable as a container that can hold a value. Variables can be declared specifically to hold certain kinds of values or data types. If you declare a variable as capable of storing Integer values, that variable would be referred to as an Integer variable. If you are not sure what kind of data the variable will hold, you can declare the variable as a Variant. A Variant is a special kind of variable that can represent anything. I'll get into the details of a Variant later in this section. Table 3.1 lists the basic data types.

Table 3.1: Data Types

Data type

Boolean Byte

Currency Date

Decimal

Double

Integer Long Object Single

String

User Defined

Range of values

True or False. 0-255-922,337,203,685,477.5808 to 922,337,203,685,477.5807.

1 January 100 to 31 December 9999 and times from 0:00:00 to 23:59:59.

With no decimal places, the largest possible value is +/-79,228,162,514,264,337,593,543,950,335. With 28 decimal places, the largest value is +/-7.9228162514264337593543950335.

-1.79769313486231 to -4.94065645841247E-324 for negative values and from 4.94065645841247E-324 to 1.79769313486232E308 for positive values.

Can have any object reference assigned to it.

-3.402823E38 to -1.401298E-45 for negative values and from 1.401298E-45 to 3.402823E38 for positive values.

A variable-length string can contain up to approximately 2 billion (2a31) characters. You can also declare fixed-length strings up to about 64,000 characters.

User-defined data types can contain one or more other data types, an array, or a previously defined user-defined type.

Memory usage

2 bytes 1 byte 8 bytes 8 bytes

12 bytes

8 bytes

2 bytes 4 bytes 4 bytes 4 bytes

Varies Varies

Variant

Varies—see section on variants later in this section.

Varies

Until you are confident with the subtleties of when to use one data type over another, I'd suggest that you focus on the following data types:

Boolean Booleans are used frequently to help implement logic in your programs. You may use them in If.. .Then statements, Do.. .While statements, and as a return type for functions. For example, in Chapter 7, you'll implement a function named WorksheetExists that returns a Boolean. This function tests to see whether or not a given worksheet name exists in a workbook.

Integer Integer variables are probably the most frequently used data type. You'll use integer variables in conjunction with For.. .Next statements, to refer to elements within an array or collection and to help navigate around a worksheet.

Long Occasionally an integer just isn't big enough and you'll need to use its big brother, the Long.

String Strings are one of the contenders for the most frequently used data type. You'll use strings to store the names of worksheets, workbooks, and named ranges among other things.

Currency, Date, and Double Occasionally, you'll also have a need for Currency, Date, and Double. Usually you'll need to use these data types when you are either implementing a function that calculates values or to store data found on a worksheet.

Before I go on to discuss the Variant data type, it is worth mentioning again that you should declare variables as precisely as possible rather than always declaring them as Variants. Declaring variables using specific data types helps to eliminate programming errors and is more efficient from a system resources standpoint.

Variant Variables

A Variant is a flexible data type that can represent any kind of value except a fixed-length string. Beginning with Excel XP or 2002, Variants even support user-defined types. Additionally, a Variant can represent the special values Empty, Error, Nothing, and Null.

If you declare a variable (see the next section "Declaring Variables") without specifying a data type, the variable is given a Variant data type by default. Likewise, if you use a variable without first declaring it, the variable is given a Variant data type.

You can test for the underlying data type of a Variant by using the VarType function. Table 3.2 shows the return values of VarType for various data types.

VarType(varname)

You may use this function to test a Variant before passing it to another procedure or block of code that assumes the variant is of a particular subtype. For example, if you have a block of code that expects to operate on an integer, you could use the VarType function.

If VarType(MyVariantVariable) = vblnteger Then ' some code specific to integers goes here End If

Another useful function for determining the underlying data type of a Variant is the TypeName function.

TypeName(varname)

TypeName returns a string that indicates the underlying data type. You could use the TypeName function in a similar manner as the VarType function just mentioned.

If TypeName(MyVariantVariable) = "Integer" Then

' some code specific to integers goes here End If

As mentioned earlier in the section, variants can also represent the following special values.

Table 3.2: Determining Underlying Data Types

Underlying data type

typeName Returns

Vartype Returns (Defined Constant)

Boolean

Boolean

11 (vbBoolean)

Byte

Byte

17 (vbByte)

Currency

Currency

17 (vbByte)

Date

Date

7 (vbDate)

Decimal

Decimal

14 (vbDecimal)

Double

Double

5 (vbDouble)

Empty

Empty

0 (vbEmpty)

Error

Error

10 (vbError)

Integer

Integer

2 (vbInteger)

Long

Long

3 (vbLong)

Null

Null

1 (vbNull)

Object

Object

9 (vbObject)

Single

Single

4 (vbSingle)

String

String

8 (vbString)

Empty This value indicates that a variant variable has been declared but no initial value has been assigned to it yet. An Empty variable is represented as zero (0) in a numeric context or a zero-length string ("") in a string context.

Error The Error value is used to indicate that an application-defined error has occurred in a procedure. This is a different kind of error from regular errors in that you, the application developer, define the error. This allows you to take some alternative action based on the error value. You create Error values by converting real numbers to error values using the CVErr function.

Nothing Nothing is actually a keyword you use to disassociate a variable from the object to which the variable referred. In this context, a variant variable that has been set to Nothing is sort of in limbo. It doesn't refer to anything, but the variable name still exists in memory.

Null Null indicates that a variable contains no valid data. To be Null, a variable must be explicitly set to Null or have participated in an operation in which one of the expressions contains Null. Do not confuse Null with Empty. Because Null values must be explicitly set, Null indicates that the variable intentionally contains no valid data. This is a subtle but important distinction.

Before we move on, you should be aware of one more important thing that Variants can do. Variants can also hold arrays. I'll discuss this important functionality in the upcoming section "Basic Array Usage." Generally the only place I use variants in an application is to hold arrays or to read data from a worksheet. In other situations, I'd advise you to use a variable of the appropriate data type.

Doing so is more efficient from a memory and processing standpoint, helps eliminate programming errors, and is much cleaner from a readability/maintainability standpoint.

0 0

Post a comment