Declaring Variables in Excel

The word Dim is short for dimension and is used to declare variables. Excel can work without major issues if you don’t Dim (declare) variables (not so if you have used Option Explicit), but performance will be affected. Undeclared variables have to be stored as Variant variables. Variant variables can accept any data type (Iteger, Range, String etc). So when you don’t declare variables Excel has to reserve much more memory than really needed for the extra processing to determine what data type your variable is. Good code has all variables dimmensioned correctly.

There are three basic levels of Dim Statements.

1. Procedure Level
Procedure Level variables must be used inside of a Procedure between
Sub MacroName()
Dim sName as String
End sub

A Procedure level variable will only retain the value passed to it while the Procedure is running, it is then destoyed.

2. Module Level
This must be placed at the very top of a Module and doing so will then make it available to ALL Procedures within that module.

3. Project Level
Project level variables have to be placed right at the top of a Standard Module. Instead of using Dim, you must use Public to declare your variable, eg Public sName as String.
The variable is then available to every Procedure in all other modules at all times. The variable ceases when you either: 1.Destroy the variable (return it to it’s default value), eg sName=””
2. Use the End statement in any Procedure.
3. Close the Workbook.

When I use Public variables I always create a Module called PublicDim and then this module is for declaring Public variables only. They are then all in one place for ease of editing.

Leave a Reply

Your email address will not be published. Required fields are marked *