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.
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:
Dim StrName As String
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.
strName or sName– will store a string
intAmount or iAmt– will store an integer
objWord or oWord– will store an object
curAmtorcAmt – 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 (negative values) 1.401298E-45 to 3.402823E38 (positive values)
8-bye floating-point number
-1.79769313486231E308 to -4.94065645841247E-324 (negative values) 4.94065675841247E-324 to 1.79769313486231E308 (positive values)
8-byte number with fixeddecimal point
-922,337,203,685,477.5808 to 922,337,203,685,477.5807
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.
Eg: 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.
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"
If Me.chkFaith = True Then
StrEnding = "Yours faithfully"
ElseIf Me.chksincere = True Then
StrEnding = "Yours sincerely"
StrEnding = ""
StrAttention = Me.txtAttn
StrDear = Me.txtDear
'run the routine to populate the letter
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.