Understanding VBA Data Types

When you create a table in Access, you need to define the data type of every field in the table. Data types in tables include things like Text (for storing short strings of text), Memo (larger chunks of text), Number (for numbers), Date/Time (for dates and times), and so forth.

VBA can work with data stored in tables. But just like tables, VBA often needs to know the type of information it's working with. As you'll see shortly, there are a couple of places in VBA code where you can define data types. You need to know what the various data types mean.

Table 4-1 lists the data types that you'll work with in VBA. The data type names are listed in the left column, each followed by a brief description. The Storage Size column shows how many bytes each data type consumes. The Declaration Character column shows an optional character that can be used at the end of a name to specify a data type. That's really more information than you need right now. Just knowing the names of the various data types is sufficient for now.

Table 4-1 VBA Data Types

Data Type

Acceptable Values

Storage Size

Declaration Character


True (-1) or False (0)

2 bytes


0 to 255

1 byte


-922,337,203,685,477.5808 to 922,337,203,685,477.5807

8 bytes



January 1, 100 to December 31, 9999

8 bytes


Table 4-1 (continued)

Data Type

Acceptable Values

Storage Size

Declaration Character


-1.79769313486231E308 to -4.94065645841247E-324 for negative values; 4.94065645841247E-324 to 1.79769313486232E308 for positive values

8 bytes



-32,768 to 32,767

2 bytes



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

4 bytes



Name of any object

4 bytes


-3.402823E38 to -1.401298E-45 for negative values; 1.401298E-45 to 3.402823E38 for positive values

4 bytes



Any text from 0 to about 2,000,000,000 characters in length

10 + string length


Variant (no text)

Any number up to the range of the Double data type

16 bytes

Variant Any text up to 2,000,000,000 22 + string length (with text) characters in length

Boring technical stuff on the Decimal data type

When perusing the VBA Help and drop-down menus, you might come across the Decimal data type. I omitted the Decimal data type from Table 4-1 because it just flat-out doesn't work. If I had included the Decimal data type in the table, its acceptable range would be +/—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 +/-0.0000000000000000000000000001. The Decimal data type's storage size would be 14 bytes, and it would have no type declaration character.

The obscure Decimal data type does exist, but you can't declare an item as being of that data type. Instead, you have to declare the item as a Variant data type with a subtype of Decimal. For example, Dim X as Variant defines a variable X as a variant; X = CDec(value) stores value in X as a Decimal data type.

In VBA code, you'll often use data types just to store little bits of information for short periods of time. The reasons for storing data with VBA code vary. One of the first places you're likely to encounter data types in VBA is when you want to pass data to, or from, your custom procedure.

0 0

Post a comment