Microsoft Office Tutorials and References
In Depth Information
Variables, Data Types, and Constants
Table 8-1: VBA Built-In Data Types
Data Type
Bytes Used
Range of Values
Byte
1 byte
0 to 255
Boolean
2 bytes
True or False
Integer
2 bytes
–32,768 to 32,767
Long
4 bytes
–2,147,483,648 to 2,147,483,647
Single
4 bytes
–3.402823E38 to –1.401298E-45 (for negative values);
1.401298E - 45 to 3.402823E38 (for positive values)
Double
8 bytes
–1.79769313486232E308 to –4.94065645841247E-324
(negative values);
4.94065645841247E-324 to 1.79769313486232E308 (for
positive values)
Currency
8 bytes
–922,337,203,685,477.5808 to
922,337,203,685,477.5807
Decimal
12 bytes
+/–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
Date
8 bytes
January 1, 0100 to December 31, 9999
Object
4 bytes
Any object reference
String (variable
length)
10 bytes + string
length
0 to approximately 2 billion characters
String (fixed
length)
Length of string
1 to approximately 65,400 characters
Variant (with
numbers)
16 bytes
Any numeric value up to the range of a double data type. It can
also hold special values, such as Empty, Error, Nothing, and Null.
Variant (with
characters)
22 bytes + string
length
0 to approximately 2 billion
User-defined
Varies
Varies by element
The Decimal data type is rather unusual because you can’t actually declare it. In fact, it
is a subtype of a variant. You need to use the VBA CDec function to convert a variant to
the Decimal data type.
Generally, it’s best to use the data type that uses the smallest number of bytes yet still can
handle all the data that will be assigned to it. When VBA works with data, execution speed is
partially a function of the number of bytes that VBA has at its disposal. In other words, the fewer
bytes used by data, the faster VBA can access and manipulate the data.
For worksheet calculation, Excel uses the Double data type, so that’s a good choice for
processing numbers in VBA when you don’t want to lose any precision. For integer calculations, you can
 
Search JabSto ::




Custom Search