What is a Variable?

When you are writing VBA code, you may need to store information at some point of your program that you may have need for at another point.

For example, you may need to store the name of your company when you are filling in a form, and then obtain that name at a later part of the code – perhaps when you end up printing a document or creating a report. In order to store this information, you need to create a container to put the information into – think of it as an empty coffee cup – waiting for you to fill it.

A variable is similar – you create an empty container in which to store information – once you have created the variable, you can then store information in that variable.

Understanding Scope

When you declare a variable, you determine its scope. The scope determines where the variable can be used in your code. You can either declare a global variable to use through-out your code, or a private variable which would then only be able to be used within a form or a specific module in your code. This article is concentrating on using a GLOBAL variable.

What Does Global Mean?

If you wanted to use a particular variable over and over again throughout your code, then you declare the variable as a PUBLIC variable and you would need to declare it in a module. In addition, you would need to use the Public statement rather than the Dim statement.

Creating a Module

In your Visual Basic Editor, click on the second button on the ribbon and select Module from the drop-down list.

Your new module will appear on the right-hand side of your screen.

NOTE: if your module does NOT have Option Explicit at the top of it, you may need to switch that option on.

Declaring Your Variables

When you create a variable, you declare it – in other words, you give it a name. This is because you can have multiple variables and you need to know which one you want to use to store each individual piece of information. As well as storing your company name, you may need to store the company address, or the telephone number. You would therefore need 3 different variables to do so.

Notice the 2 boxes at the top of the screen – the left hand box is the Object box and the right hand box is the Procedure box – notice that the procedure box says (Declarations) which indicates that you are in the declaration part of your module (in other words at the top, before you have written any other code). This is important – in order for your variable to be Global, it MUST appear at the top of the module under the Option Explicit statement, and it MUST be declared as a PUBLIC variable (as opposed to the DIM statement for a private form or module-based variable).

Once you have typed Public, you can decide the name for your variable. The name that you chose for your variable can reflect what type of data you are going to store in the variable. It is useful to use the RVBA (Reddick VBA) naming conventions as in strCompanyName which indicates that the variable is going to be storing a TEXT STRING (str) as opposed to numbers or dates for example.

The Reddick VBA Naming Conventions

When you are deciding the name of your variable, you need to consider that variable names must begin with an alphabetic character, must be unique within the same scope and can’t be longer than 255 characters. You cannot have any spaces in the variable name.

You then need to select the data type for your variable. Once you have typed the name and then typed “as” and pressed the space bar, a list of available objects, constants and data types will appear. The data types are easily identified as a blue window type box to the left of each data type.

Available Data Types

Visual Basic supports the following data types:

Using a Global Variable

Once you have declared your variables, you can use them throughout your code to store information

If, for example, you have 2 separate procedures that need to use the company name, you can use the same variable in both these procedures.

In the example below, the Company Name variable is declared at the top of the module – and can then be used in both TestVariable and TestVariable2

Option Explicit
Public strCompanyName As String
Public strAddress As String
Public strPhone As String

Sub TestVariable()
    'enter information into the variable
    strCompanyName = "ABC Company"
    'show the information on the screen
    MsgBox strCompanyName, vbInformation
End Sub

Sub TestVariable2()
    'enter information into the variabl
    strCompanyName = "XYZ Company"
    'show the information on the screen
    MsgBox strCompanyName, vbInformation
End Sub

Where the result for 1 will be:

And for 2 would be:

Global variables are also very useful in keeping information in memory while you use the macro/program that has been created. If, for example, you enter the name of the company in one form, and then perhaps you have a Next button the form, or a Print button – to move to another form, or to print a report, the information that you have entered in the first form will be taken through to the next form, or to the report that you want printed.

Say, for example, you have a form at the beginning of your program – and you enter the company name in that form – and then you click the Next button as shown in the example below

The code behind the next button will store the company name, company address and phone number in the variables that were declared in the global module. They do not have to be re-declared in the form.

Option Explicit
Private Sub cmdNext_Click()
    strCompanyName = Me.txtCompany
    strAddress = Me.txtAddress
    strPhone = Me.txtPhone
    frmCompany.Show
    Unload Me
End Sub

The next form will then show the company name as its caption – even though the first form as now closed. The variable is being taken through from the first form to the second form.

This is because it is a GLOBAL variable.

As you can see, global variables are a very neat and efficient way to control the data input in VBA. It is important that they are always declared at a module level in order that you can use them throughout the project that you are coding.

It is always very useful to give them a significant name (for example, strCompanyName) which lets you know what you want to store in the variable. It is also recommended that you always declare all your global variables in the same module in order to keep track of what you have declared.