In most programming languages, data types are specified for each variable, such as String, Integer, Date, Boolean, etc. In VBA, you specify the data type when you declare a variable with the As keyword:
To create concise and fast code, it is recommended to explicitly specify a data type. If you use Option Explicit it will force all of your variables to have explicit data types. You can of course declare a variable without specifying a data type. In this case, VB assigns the variable to a special data type called Variant.
When you do not specify a data type for a variable, the variable is assigned to the data type called Variant. The Variant is a special type of variable that can store a value of any type. Depending on the value and the context VBA will automatically assign the Variant one of the following sub-types: Null, Numeric, String, Date/Time, Boolean, or Object.
A NULL subtype is an unusual subtype that is used in conjunction with databases. NULL refers to a field that contains no data. NULL does not mean zero, or even empty. Zero is a valid value, and empty is a data type that has nothing assigned to it yet. NULL means “nothing”, no data and no data type.
Numeric Data Types
Numeric Data Types include the following:
Integer – Whole numbers within the range of -32,768 to 32,767.
The Object data type stores a reference to an object. Objects will be discussed further a bit later in this supplement.
It is important to establish a good naming convention for your variables. Your naming convention should be easily understood by another developer who might look at your code later. Some tips for naming:
NameFirst and NameLast are better than FirstName and LastName because they will appear together in a search.
Within a procedure or function you may want to prefix your variables with a p_ or f_ type code letter.
Create names with multiple words, capitalizing the first letter of each word.