Excel

VBA Data Types

Data type refers to how data is stored in memory, i.e. strings, integers, real numbers, booleans etc. VBA does not required you to explicitly define the data type for a variable because it can automatically handle the data implicitly by assigning the Variant data-type which is the default data type for a variable. But specifying data-type for a variable is a good idea and it has advantages, such as, efficient use of memory, fast execution of code, and compile time error checking.
Following table explains the data types that VBA supports and the amount of memory each variable type requires:

Date Type Size Range of Values
Boolean 2 bytes True or false
Byte 1 byte 0 to 255
Currency 8 bytes -922,337,203,685,477.5808 to 922,337,203,685,477.5807
Date 8 bytes January 1, 100, to December 31, 9999
Decimal 14 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
Smallest non-zero number is+/-0.0000000000000000000000000001
Double 8 bytes -1.79769313486231E308 to -4.94065645841247E-324 for negative values
4.94065645841247E-324 to 1.79769313486232E308 for positive values
Integer 2 bytes -32,768 to 32,767
Long 4 bytes -2,147,483,648 to 2,147,483,647
LongLong (available on 64-bit systems) 8 bytes -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
LongPtr 4 bytes on 32-bit systems -2,147,483,648 to 2,147,483,647 (same as Long)
8 bytes on 64-bit systems -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 (same as LongLong)
Object 4 bytes Any Object reference
Single 4 bytes -3.402823E38 to -1.401298E-45 for negative values
1.401298E-45 to 3.402823E38 for positive values
String (variable length) 10 + string length 0 to approximately 2 billion
String (fixed length) Length of string 1 to approximately 65,400
Variant (with numbers) 16 bytes Any numeric value up to the range of a Double. It can also hold special values, such as Empty, Error, Nothing, and Null.
Variant (with characters) 22 bytes + string length (on 32-bit systems)
24 bytes + string length (on 64-bit systems)		 0 to approximately 2 billion (Same as variable-length String)

Type declaration characters

A "type declaration character" is a character that you add to the end of a variable's name in an implicit declaration to tell VBA which data type to use for the variable. Following table lists the type declaration characters:

CharacterData type
%Integer
&Long
@Currency
!Single
#Double
$String

Implicitly declaration of variables

You could implicitly declare the String variable name by using the $ type-declaration character for String: 

Sub test ()
 name$ = "BrainBell"
End Sub

You could implicitly declare the Currency variable price by using the at @ type-declaration character for Currency:

Sub test ()
 price@ = 100.00
End Sub

Explicit declaration of variables

You could explicitly declare the String variable name with the following statement, which assigns the value BrainBell to the variable:

Sub test ()
 Dim name as String
 name = "BrainBell"
End Sub

You could explicitly declare the Currency variable price by using this statement:

Sub test ()
 Dim price as Currency
 price = 100.00
End Sub