VBA Data Types - Microsoft Office

Data Types

A Data Type refers to how the data is stored in memory and how much space it takes up.
The data type of a variable (or object) tells the compiler the size of the object (ie the number of bytes to use).
You should always try and use the data type that uses the smallest number of bytes to get the job doen.
It is always best to use the data type that uses the smallest amount of space (i.e. bytes) yet can still handle all the data assigned to it.
The data type of a variable determines how the value is represented in memory.
The smaller the bytes the faster it executes.
You should use the data type conversion functions instead of using the Val() function when you want to convert from one data type to another.
All variables must have an associated data type which determines what data can be stored in it.

Data Type	Default	Range	Size	Abr.	Comments
Boolean	False	True (-1) or False (0)	2 bytes (16 bits)		(b) The zero value is treated as False and all other values are treated as True. Use NOT to toggle
Byte		0 to 255	1 byte (8 bits)		(by) integers often used in conjunction with string operations Use Long instead
Currency	0	- 9.2 E+14 to + 9.2 E+14	8 bytes (64 bits)	@	(c) scaled integer Use Double instead
Date		Jan 1 100 to Dec 31 9999	8 bytes (64 bits)		(dt) floating point numbers Use Long instead
Decimal*		- 7.9 E+28 to + 7.9 E+28	14 bytes		(dec) Variant subtype Must be declared as a Variant and then converted. Converted using CDec
Double	0	- 1.8 E+308 to + 4.9 E+324	8 bytes (64 bits)	#	(db) double precision floating point used for high accuracy
Integer	0	- 32,768 to + 32,767	2 bytes (16 bits)	%	(i) Use Long instead
Long	0	- 2.1 E+9 to + 2.1 E+9	4 bytes (32 bits)	&	(l) long integer
LongLong	0	-9.2 E18 to +9.2 E18	8 bytes (64 bits)		Added in Office 2010, VBA 7.0
LongPtr					Added in Office 2010, VBA 7.0 Long on 32 bit LongLong on 64 bit
Object	Nothing		4 bytes (32 bits)		(ob) stored as a reference to an object.
Single	0	- 1.3 E+38 to + 1.3 E+38	4 bytes (32 bits)	!	(sng) single precision floating point used when you have fractional numbers Use Double instead
String	""		10 bytes + length	$	(s) fixed and variable length
User Defined					(ud)
Variant	Empty		16 - 22 bytes	$	(v) any data type, typically used for arrays or variants

Abbreviations

These are also known as Type Declaration Suffixes
It is possible declare variables by appending a special character to the end of the variable name.

Dim iNumber% 'integer

What is the Stack ?

A stack stores items on a last in first out basis
This is where local variables are stored.
Value Types - An area is set aside for the value on the stack and this is referred to by the name of the variable.

What is the Heap ?

Reference Types are allocated on the heap
When an object is allocated on the heap its address is returned and the address is assigned to a reference.