VBA Data Types

When you declare a variable, you should also identify its data type. You’re probably already very familiar with data types because you assign data types to table fields. VBA uses the same data types to define a variable.

To name the variable in VBA, you need to follow the following rules.

  • It must be less than 255 characters
  • No spacing is allowed
  • It must not begin with a number
  • Period is not permitted

 

The most important job of a data type is to ensure the validity of your data. Specifying a data type won’t keep you from entering an invalid value, but it will keep you from entering an invalid type. If you omit the data type, VBA applies the Variant data type to your variable—it’s the most flexible and VBA won’t guess at what the data type should be. Below Table compares VBA’s many data types.

 

VBA Data Type Comparison

 

Data Type or Subtype Required Memory Default Value VBA Constant Range
Integer 2 bytes 0 vbInteger –32,768 to 32,767
Long Integer 4 bytes 0 vbLong –2,147,483,648 to 2,147,486,647
Single 4 bytes 0 vbSingle –3402823E38 to –1.401298E–45 or 1.401298E–45 to 3.402823E38
Double 8 bytes 0 vbDouble –1.79769313486232E308 to –4.94065645841247E–324 or 1.79769313486232E308 to 4.94065645841247E–324
Currency 8 bytes 0 vbCurrency –922,337,203,477.5808 to 922,337,203,685,477.5807
Date 8 bytes 00:00:00 vbDate January 1, 100 to December 31, 9999
Fixed String String’s length Number of spaces to accommodate string vbString 1 to 65,400 characters
Variable String 10 bytes plus the number of characters Zero- length string (“”) vbString 0 to 2 billion characters
Object 4 bytes Nothing (vbNothing) vbObject Any Access object, ActiveX component or Class object
Boolean 2 bytes False vbBoolean –1 or 0
Variant 16 bytes Empty (vbEmpty) vbVariant Same as Double
Decimal 14 bytes 0 vbDecimal -79,228,162,514,264,337,593,543,950,335 to 79,228,162,514,264,337,593,543,950,335 or –7.2998162514264337593543950335 to 7.9228162514264337593543950335
Byte 1 byte 0 vbByte 0 to 255

 

 

Excel VBA Data-Types

 

Computer cannot differentiate between the numbers (1,2,3..) and strings (a,b,c,..). To make this differentiation, we use Data Types.

 

VBA data types can be segregated into two types

 

  • Numeric Data Types
Type Storage Range of Values
Byte 1 byte 0 to 255
Integer 2 bytes -32,768 to 32,767
Long 4 bytes -2,147,483,648 to 2,147,483,648
Single 4 bytes -3.402823E+38 to -1.401298E-45 for negative values 1.401298E-45 to 3.402823E+38 for positive values.
Double 8 bytes -1.79769313486232e+308 to -4.94065645841247E-324 for negative values 4.94065645841247E-324 to 1.79769313486232e+308 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 if no decimal is use +/- 7.9228162514264337593543950335 (28 decimal places)
  • Non-numeric Data Types
Data Type Bytes Used Range of Values
String (fixed Length) Length of string 1 to 65,400 characters
String (Variable Length) Length + 10 bytes 0 to 2 billion characters
Boolean 2 bytes True or False
Date 8 bytes January 1, 100 to December 31, 9999
Object 4 bytes Any embedded object
Variant(numeric) 16 bytes Any value as large as Double
Variant(text) Length+22 bytes Same as variable-length string

In VBA, if the data type is not specified, it will automatically declare the variable as a Variant.