Data Types

When you create Visual Basic procedures, you have a purpose in mind. You want to manipulate data. Because your procedures will handle different kinds of information, you should understand how Visual Basic stores data. The term data type determines how the data is stored in the computer's memory. For example, data can be stored as a number, text, date, object, etc. If you forget to tell Visual Basic the type of your data, Visual Basic assigns the Variant data type. The Variant type has the ability to figure out on its own what kind of data is being manipulated and then take on that type. The Visual Basic data types are shown in Table 3-1. In addition to the built-in data types, you can define your own data types. (You will see an example of a user-defined data type in Chapter 8.) Because data types take up different amounts of space in the computer's memory, some of them are more expensive than others. Therefore, to conserve memory and make your procedure run faster, you should select the data type that uses the least amount of bytes and, at the same time, is capable of handling the data that your procedure has to manipulate.

Table 3-1: The VBA data types

Data Type (Name) Size (Bytes) Description

Boolean

2

A logical value of True or False

Byte

1

Integer from 0 to 255

Integer

2

Integer from -32,768 to 32,767

Long

4

Integer from -2,147,483,648 to 2,147,483,647

Single

4

Single precision floating-point real number: Negative numbers: -3.402823E38 to -1.401298E-45

Positive numbers: 1.401298E-45 to 3.402823E38

Data Type (Name) Size (Bytes) Description

Double

8

Double precision floating-point real number:

Negative numbers: -1.79769313486231E308 to

-4.94065645841247E-324

Positive numbers: 4.94065645841247E-324 to

1.79769313486231E308

Currency

8

(scaled integer) Used in fixed-point calculations: -922,337,203,685,477.5808 to 922,337,203,685,477.5807

Decimal

14

+/-79,228,162,514,264,337,593,543,950,335 with no decimal point;

+/-7.9228162514264337593543950335 with 28 places to the right of the decimal; smallest non-zero number is +/-0.0000000000000000000000000001

Date

8

Date from January 1, 100, to December 31, 9999

String

(variable-length)

10 bytes + string length

A variable-length String can contain up to approximately 2 billion characters.

String

(fixed-length)

Length of string

A fixed-length String can contain up to approximately 65,400 characters.

Object

4

Object variable used to refer to any Excel object

Variant

(with numbers)

16

Any numeric value up to the range of a Double

Variant

(with characters)

22 bytes + string length

Same range as for a variable-length String

User-defined (using Type)

Number required by elements

The range of each element is the same as the range of its data type.

0 0

Post a comment