A variable lets you store a value while your code is being executed.
A variable is just a named storage location in memory. At the top of all your modules you should have "Option Explicit" to force explicit declaration.
Variables that are not defined explicitly have the "Variant" datatype assigned to them by default.
Variables can accommodate a wide variety of datatypes from simple boolean values to extremely large 20 digit numbers.
Scope - The scope of a variable defines which procedures can use that variable.
Lifetime - The lifetime of a variable defines how long that variable retains the values assigned to it.


The variant datatype should only be used when you don't know in advance the type of the variable.
Declaring variables with the "Dim" statement inside a procedure means they are local to the procedure and are reinitialised to default values each time the procedure is executed.
The value of a variable may change during the life of the program.
For instance a variable that holds a text string has the datatype String and is called a string variable.
In VBA each variable has a specific data type, which indicates which type of data it can hold.
For more details please refer to the dedicated Data Types section.

Explicitly Declare Variables

Variables should always be declared explicitly as it ensures that spelling mistakes are picked up and ensures that memory is used efficiently.
The "Variant" datatype uses a minimum of 16 bytes and is very slow. Using a list of comma-separated variables does not work.
You do not have to explicitly declare your variables, you can let Visual Basic do this for you.
Any variables that are created automatically have the "Variant" datatype and can contain any type of data.
VBA automatically converts the data to the proper type when it is used.
You should explicitly initialise the variables and not rely on the system defaults.
You code will run a lot slower since a variant datatype uses up more memory.

Variable Scope

The scope controls the lifetime and visibility of the variable. It is good programming practice to use the most restrictive scope possible for your variables.
You can also declare variables using the public and private keywords to explicitly indicate this scope.
Procedure / Local - variables and constants are local and are declared either using "Dim" or "Static" at the start of a procedure. A local (or procedure-level) variable or constant is declared within the procedure and is not visible outside of that procedure.
Module ("m" prefix) - variables and constants are declared as Private at the top of a module (always preceded by m). Module level variables are declared in the declaration section of a code module (standard, class or userform). These variables should be used if you want several procedures to have access to the same variable.
Global ("g" prefix) - variables are declared as Public at the top of a module level (always preceded by g). Public variables constants, procedures and functions are visible from any project that references the project where they are declared. This keyword is only supported for backwards compatibility and should not be used. Any Global keywords should be replaced with the word "Public".


Always explicitly declare all your variables using Option Explicit.
Any variables that are declared as Public in the declaration section of a class module can be accessed from anywhere in the project once an object reference has been made to the class.
If a variable exceeds the range of its datatype you will receive an overflow error.
Always use meaningful descriptive variable names.
You can use the Global keyword but this is only made available for backwards compatibility.
The default scope for variables is Private when you use Dim at a module level.

© 2017 Better Solutions Limited. All Rights Reserved. © 2017 Better Solutions Limited