Excel

VBA Data Types Examples

The term data type refers to the manner in which VBA stores data in memory. VBA can automatically assign data type to variables, it does so at a cost. Letting VBA handle your data typing results in slower execution and inefficient memory use for large or complex applications. So declaring variables and choosing data types that use the smallest number of bytes are definitely a good practice. In last tutorial we briefly discussed the VBA's built-in data types. Now I'll try to explain each data type through examples.

Byte

Bytes used: 1 byte, Range of values: 0 to 255

A Byte variable holds unsigned (non-negative) 8-bit (1-byte) integers and takes up the least memory of any data type that range in value from 0 through 255. You can declare and initialize a Byte variable by assigning it a decimal literal or a hexadecimal literal.

The Byte data type has no type-declaration character.

Sub test ()
 Dim byteValue As Byte

 'Decimal literal
 byteValue = 201

 'Hexadecimal literal
 byteValue = &HC9
End Sub

If the integral literal is outside the range of a Byte (either a negative value or a value higher to 255), “Run-time error ‘6’: Overflow” error occurs:

Run-time error 6 : Overflow

Boolean

Bytes used: 2 bytes (16-bit), Range of values: True or False

Holds values that can be only True or False. The keywords True and False correspond to the two states of Boolean variables.

The Boolean data type has no type-declaration character.

Sub test ()
 Dim bool1 as Boolean
 Dim bool2 as Boolean
 bool1 = True
 bool2 = False
End Sub

Integer

Bytes used: 2 byte (16-bit), Range of values: -32,768 to 32,767

The Integer data type is the most efficient way of handling numbers within its range, a range that makes it useful for many procedures.

The type-declaration character for Integer is the percent % sign.

Sub test () 
 Dim integer1 as Integer
 Dim integer2%

 integer1 = -32768
 integer2 = 32767
End Sub

Implicitly declaring integer variables:

Sub test () 
 integer1% = -32768
 integer2% = 32767
End Sub

Long

Bytes used: 4 byte (32-bit), Range of values: -2,147,483,648 to 2,147,483,647

A Long can hold integer values larger or smaller than those the Integer data type can handle, so use the Long data type to contain integer numbers that are too large to fit in the Integer data type.

The type-declaration character for Long is the ampersand & sign.

Sub test () 
 Dim long1 as Long
 Dim long2&

 long1 = -2147483648
 long2 = 2147483647
End Sub

Implicitly declaring long variable:

Sub test () 
 long1& = -2147483648
 long2& = 2147483647
End Sub

Single

Bytes used: 4 byte (32-bit),
Range of negative values: –3.402823E38 to –1.401298E-45
Range of positive values : 1.401298E-45 to 3.402823E38

Single-precision numbers store an approximation of a real number. Use the Single data type to contain floating-point values that do not require the full data width of Double data type.

The type declaration character for Single is the exclamation ! sign.

Sub test ()
 Dim single1 as Double 
 Dim single2!

 single1 = -3.402823E38
 single2 = -1.401298E-45
End Sub

Implicitly declaring single variables:

Sub test ()
 single1! = 1.401298E-45
 single2! = 3.402823E38
End Sub

Double

Bytes used: 8 byte,
Range of negative values: -1.79769313486231E308 to -4.94065645841247E-324
Range of positive values : 4.94065645841247E-324 to 1.79769313486231E308

Double here stands for double-precision floating point. Double-precision numbers store an approximation of a real number. The Double data type provides the largest and smallest possible magnitudes for a number.

The type declaration character for Double is the hash (or number) # sign.

Sub test ()
 Dim double1 as Double 
 Dim double2#

 double1 = -1.79769313486231E308
 double2 = 1.79769313486231E308
End Sub

Implicitly declaring double variables:

Sub test ()
 double1# = -1.79769313486231E308
 double2# = 1.79769313486231E308
End Sub

Currency

Bytes used: 8 byte,
Range of values: –922,337,203,685,477.5808 to 922,337,203,685,477.5807

A Currency variable holds signed (positive and negative) 64-bit (8-byte) numbers in integer format. This data type is designed for use with money. It allows for positive and negative numbers with up to 15 digits to the left of the decimal point and 4 digits to the right of it. Unlike the Single and Double data types, the Currency data type is exact, not rounded.

The type declaration character for Currency is the at @ sign.

Sub test()
 Dim currency1 as Currency
 Dim currency2@

 currency1 = 800.59
 currency2 = 100.00

End Sub

Following example demonstrates the implicitly declaration of Currency variable using its type-declaration character @:

Sub test()
 currency1@ = 800.59
 currency2@ = 100.00
End Sub

Decimal

Bytes used: 14 byte (112-bit),
Range of values (with no decimal point):
+/–79,228,162,514,264,337,593,543,950,335

Range of values (with 28 places to the right of the decimal):
+/–7.9228162514264337593543950335

The Decimal data type provides the greatest number of significant digits for a number. It is particularly suitable for calculations, such as financial, that require a large number of digits but cannot tolerate rounding errors.

The Decimal data type is unusual because you can’t 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.

Sub test ()
 Dim dec as Variant
 dec = Cdec (34343434344)
End Sub

Date

Bytes used: 8 byte, Range of values: January 1, 0100 to December 31, 9999

Date data type holds 64-bit (8-byte) values that represent dates ranging from January 1 of the year 0100 through December 31 of the year 9999, and times from 12:00:00 AM (midnight) through 11:59:59 PM. VBA works with dates and times as floating-point numbers, with the date displayed to the left of the decimal point and the time to the right.

Date has no associated type-declaration character.

Sub test ()
 Dim onlyDate as Date
 Dim onlyTime as Date
 Dim dateTime as Date

 onlyDate = #10/31/2019#
 onlyTime = #12:59:00 PM#
 dateTime = #10/31/2019 12:59:00 PM#
End Sub

Following example demonstrates the implicitly declaration of Date variables:

Sub test ()
 onlyDate = #10/31/2019#
 onlyTime = #12:59:00 PM#
 dateTime = #10/31/2019 12:59:00 PM#
End Sub

Object

Bytes used: 4 byte, Range of values: An object reference

The Object data type is for storing addresses that reference objects (for example, objects in an application’s object model, such as ActiveSheet), providing an easy way to refer to an object. Using the Set statement, a variable declared as an Object can have any object reference assigned to it.

Sub Test ()
 Set actSht = ActiveSheet
 MsgBox (actSht.Name)
End Sub

String

Bytes used (variable length): 10 bytes + string length,
Range of values: 0 to approximately 2 billion characters

The String data type is for handling text. Strings can contain letters, numbers, spaces, and punctuation. The codes for String characters range from 0 – 255. The first 128 characters (0 – 127) of the character set correspond to the letters and symbols on a standard U.S. keyboard. These first 128 characters are the same as those defined by the ASCII character set. The second 128 characters (128–255) represent special characters, such as letters in international alphabets, accents, currency symbols, and fractions.

The type-declaration character for String is the dollar $ sign.

Sub testString ()
 Dim str1 as String
 Dim str2 as String
 Dim str3 as String

 str1 = "1st string"
 str2 = "2.2"
 str3 = "False"
End Sub

Following example demonstrates the implicitly declaration of String variables using their type-declaration character $:

Sub testString ()
 str1$ = "1st string"
 str2$ = "2.2"
 str3$ = "False"
End Sub

String (fixed length)

Fixed-length String variables can contain from 1 to about 64,000 characters. If the data assigned to the String variable is shorter than the fixed length, VBA pads the data with trailing spaces to make up the full complement of characters. If the text assigned to a fixed-length String variable is longer than the fixed length, VBA truncates the text, lopping off the necessary characters from the right end of the string.

Following example demonstrates how to declare a five characters fixed length string:

Sub testStringFixed ()
 Dim str As String * 5
 str = "abc12"
End Sub

Variant

Bytes used (String Variant) : 22 bytes + string length,
Range of values: 0 to approximately 2 billion

Bytes used (Numeric Variant): 8 byte,
Range of negative values: -1.79769313486231E308 to -4.94065645841247E-324
Range of positive values : 4.94065645841247E-324 to 1.79769313486231E308

Variants can store any type of data. Variant is the default type of variable. Any time you declare a variable and do not specify its type (or declare a Function procedure and do not declare the data type it must return), you are telling VBA that it must create a Variant type variable (or return a Variant value).

A variant data-type can hold any kind of data (except fixed-length String data) such as numbers, text, dates, arrays and objects. A Variant can also contain the special values Empty, Error, Nothing, and Null.

A declaration such as Dim integer1 creates a Variant. However, Dim integer2 As Integer creates a variable of the Integer data type:

Sub test ()
 'Variant data type
 Dim integer1

 'Integer data type
 Dim integer2 As Integer 
End Sub

You also can declare a Variant variable explicitly:

Sub test ()
 Dim variantVar As Variant
End Sub

Note:
Variant type reserves additional memory, meaning that for long operations, your processor will do more effort to manipulate its data. So it is recommend that you must declare the variables with the exact type they will hold (or even better, with the smaller possible data type, so it can use the minimum amount of memory necessary to store it content).

The Variant data type has no type-declaration character.