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.

For example:

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.

Naming Conventions

Typically the beginning of a variable name will define what type of data is going to be stored in that variable.

For example

strName or sName– will store a string

intAmount or iAmt– will store an integer

objWord or oWord– will store an object

curAmt or 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.

Data Types

A variable can contain data from any supported data type.  Visual Basic supports:

Data Type	Description	Range
Byte	1-byte binary data	0-255
Integer	2-byte integer	-32,768 to 32,767
Long	4-byte integer	-2,147,483,648 to 2,147,483,647
Single	4-byte floating-point number	-3.402823E38 to –1.401298E-45 (negative values) 1.401298E-45 to 3.402823E38 (positive values)
Double	8-bye floating-point number	-1.79769313486231E308 to -4.94065645841247E-324 (negative values) 4.94065675841247E-324 to 1.79769313486231E308 (positive values)
Currency	8-byte number with fixed decimal point	-922,337,203,685,477.5808 to 922,337,203,685,477.5807
String	String of characters	Zero to approximately two billion characters
Variant	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
Boolean	2 Bytes	True or False
Date	8-byte date and time	1 January 100 to 31 December 9999
Object	4-bytes	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.

Understanding Scope

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).

For example:

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.

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 " & 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.