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" data type assigned to them by default.
Variables can accommodate a wide variety of data types 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 data type 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 data type 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" data type 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 data type 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.


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 data type 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.

© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopNext