All About Dim Statements in VBA
A variable is a named storage location in which you can store information while your macro/program is running. Each variable has a unique name where the name can identify what type of variable it is by using standard naming conventions.
A variable can contain different types of data – text, dates, or numbers, for example. Variable names must begin with an alphabetic character, cannot be longer than 255 characters, and cannot contain an embedded full-stop. The names must also be unique within the scope of the variable.
Scope refers to the availability of a variable, constant, or procedure for use by another procedure. There are three scoping levels: procedure-level, private module-level, and public module-level. We are going to focus on the procedure-level or private module-level variables – the public module-level variable uses a GLOBAL variable. You can read more about that in our article on global variables.
Declaring a Variable
When you declare procedure-level or private module-level variables, you use a
Dim statement. A declaration statement can be placed within a procedure to create a procedure-level variable, or it may be placed at the top of a module, in the Declarations section, to create a module-level variable.
To declare a variable, you must first decide what the variable is going to be used for (what information the variable is going to store), then use the relevant naming convention for the variable. This makes it easy to see at a glance the type of data the variable is going to store.
To declare a string variable (ie one that stores text) within a procedure, you can declare the variable as follows:
Sub DataInput() Dim StrName As String End Sub
You use the
As keyword to specify the data type for the variable as shown above.
Typically the beginning of a variable name will define what type of data is going to be stored in that variable.
sName– will store a string
iAmt– will store an integer
oWord– will store an object
cAmt – will store currency
And so on..
When you declare a variable, the Visual Basic AutoComplete will select the variable type from the drop down list available:
Pressing enter or the space bar will complete the text for you.
A variable can contain data from any supported data type. Visual Basic supports:
|1-byte binary data
|-32,768 to 32,767
|-2,147,483,648 to 2,147,483,647
|4-byte floating-point number
|-3.402823E38 to –1.401298E-45
1.401298E-45 to 3.402823E38
|8-bye floating-point number
|8-byte number with fixed decimal point
|String of characters
|Zero to approximately two billion characters
|Date/time, floating point number, integer, string or object. 16 bytes plus 1 byte for each character if the value is a string value
|Date values: January 1, 100 to December 31, 9999
Numeric values: same range as Double
String values: same range as String
Can also contain Error or Null values
|True or False
|8-byte date and time
|1 January 100 to 31 December 9999
|Any object reference
It is not mandatory to declare variables before they are used in a procedure, but it is good programming practice to do so to prevent errors in your code occurring. To make variable declaration forced you can use the
Option Explicit statement in the Declarations area of a module or form. If you have this option set, then if you try and use a variable that has not been declared, an error will occur.
You can have Option Explicit automatically added to the beginning of each new module by setting it in the Options of Visual Basic.
Click on the Tools menu, Options and then select the Editor tab.
Make sure Require Variable Declaration is switched on, and click OK.
Now when you create a new module or user form, Option Explicit will appear in the Declarations area of the module or form.
Scope refers to the availability of a variable, constant, or procedure for use by another procedure. There are three scoping levels: procedure-level, private module-level, and public module-level.
You determine the scope of a variable when you declare it. It’s a good idea to declare all variables explicitly to avoid naming-conflict errors between variables with different scopes.
If a variable is declared at procedure level, the variable can be used only in that procedure. If the variable is declared in the Declarations section of the module, the variable is available to all procedures within the module, but not to procedures in other modules in the project. To make this variable available to all procedures in the project, you would need to precede it with the Public statement (see article on global variables).
This variable is declared within the DataInput procedure. It has therefore been declared at procedure level and is only available within this procedure.
A variable defined within a procedure is not visible outside that procedure:
This variable has been declared in the Declarations area of the module, and is therefore available to all procedures within this module.
You can also use the
Private statement to declare private module-level variables.
Private StrName As String
Private variables can be used only by procedures in the same module.
Note: When used at the module level, the
Dim statement is equivalent to the
Private statement. You might want to use the
Private statement to make your code easier to read and interpret.
HOT TIP: The Public and Private keywords do not only refer to variables – they refer to constants and procedures as well. All procedures are public by default, except for event procedures.
When Visual Basic creates an event procedure (for example the click event), the Private keyword is automatically inserted before the procedure declaration. For all other procedures, you must explicitly declare the procedure with the Private keyword if you do not want it to be public.
Using a Variable in an Example
Using Word VBA, we may have created a letterhead form as follows:
We then need to write the code behind the OK button to populate the form. We are going to declare 4 module level variables below the Option Explicit Statement, and then we are going to populate those variables using the OK click event procedure. This procedure will run when the OK button is clicked.
Once the variables are populated, the routine
PopulateLetter will push the information that is then contained in the variables back into the Word letterhead template that we would have set up for this purpose.
Option Explicit Dim StrEnding As String Dim StrPost As String Dim strAddress As String Dim strDear As String Private Sub cmdok_Click() 'Populate the variables StrAddress = Me.txtTo If Me.chkFax = True Then StrPost = "BY FAX " &amp; Me.txtFax ElseIf Me.chkHand = True Then StrPost = "BY HAND" ElseIf Me.chkPost = True Then StrPost = "BY EMAIL" End If If Me.chkFaith = True Then StrEnding = "Yours faithfully" ElseIf Me.chksincere = True Then StrEnding = "Yours sincerely" Else StrEnding = "" End If StrAttention = Me.txtAttn StrDear = Me.txtDear 'run the routine to populate the letter PopulateLetter Me Unload Me End Sub
Therefore, as you can see, variables are a vital part of VBA code, and are used within all programming languages. They give you the flexibility to carry information through your code, from one module to another, or from a form to a module, and then back to your workbook or document.
The scope of the variable is vital in the role that the variable will play in your code. As we have learnt, the
Dim statement is the declaration statement for a private variable and is used within your form or your private module level VBA code.