Home > Articles > Data > Access

  • Print
  • + Share This
This chapter is from the book

Declaring Variables

To declare a variable means to write a line of code that contains a name and a data type. In Hour 2, "Exploring VBA," you learned how to use Dim statement variable declarations, but there are a few other declarations you need to learn. As a refresher, the Dim variable declaration takes the following form:

Dim VariableName As DataType

VariableName is the meaningful name you use to refer to the data in your code, and DataType is the actual type of data you store in that variable.

You already know about the Dim variable declaration, so you'll focus most of your energies on the new declaration types in the next section.

The four basic types of declarations are Dim, ReDim, Const, and Global. The following subsections describe each type, in turn, and offer examples so that you can practice declaring variables.

Dim Variables

In Hour 2, you learned that the Dim keyword is the most basic kind of variable declaration. By using Dim, you are instructing the compiler to assign memory to the name in the Dim statement.

Currently, the memory size of an Integer type is 16 bits, or 2 bytes, of memory. This number of bits means you can store about 4 billion different values in an Integer.


There are 8 bits in a byte of memory. Therefore, a 2-byte data type contains 16 bits. The mathematics of permutations says that you can raise 2 to the power of the number of elements in a set, in this case 16, thus yielding the number of combinations. The calculation is 216 = 65536. Accounting for both positive and negative numbers, a 16-bit integer data type can store values between -32768 and +32767. The easiest way to cope with data type limitation is to use the Access Help file.


Remember these three data types when getting started: Use String for text, Integer for numbers without decimals, and Double for numbers with decimal places.

Although it isn't important to know how much memory is assigned to variables, it is important to know the data types available for variables and what values are allowable for these data types. This is a job best left to the Access Help file.

The three most commonly used data types are String, Integer, and Double. The String type is used for text. The Integer type is used for numbers from values ranging from positive 32,767 to negative 32,767, and the Double type is used for very small or very large, positive and negative numbers with decimal places.

ReDim Variables

The ReDim declaration type is used for arrays of data. An array of data is data that is contiguously stored in memory and accessed by an index. Arrays are used when you have more than one value of a similar type. For example, if you want a variable that contains 10 social security numbers, you might use an array of the String type. The following example demonstrates the syntax for ReDim:

ReDim ArrayVariableName(NumberOfElements) As DataType

You place the ReDim keyword first, followed by the name of the array, followed by the number of elements in parentheses. At the end of the line is the now familiar As keyword and the data type. The next line of code is an actual example of an array declaration using ReDim.

ReDim SSN(5) As String

This code fragment is interpreted as a variable array of five strings whose size can be changed. ReDim is used only with arrays. When you use ReDim to declare an array, you are indicating to Access that you might change the number of elements that the array can store.


You can use Dim to declare an array, but you can't change the number of elements after you declare an array with Dim. ReDim enables you to enlarge or shrink the array as needed.

Arrays are useful when you want to store more than one of a similar kind of data in memory. To use an array, you need to know ReDim. Hour 12, "Managing Varying Amounts of Data," covers arrays in detail.

Const Variables

Use Dim when you want to declare a variable whose value can be changed by the program. Use Const when you mean exactly the opposite—that is, the value of the variable that can never be changed.

There are perfect examples of constant values in the real world. The value for pi, for example, is approximately 3.1459. If you were using pi in an equation, you might want to declare it using Const. Here's the syntax for Const:

Const ConstName = Value

The Const keyword is followed by the name of the constant. You then insert the equals operator and a value of the appropriate data type. The compiler determines the data type automatically by the value you assign the constant. Therefore, you don't need to indicate the data type literally. To declare PI using Const, you would write the following:

Const PI = 3.1459


A popular practice among programmers is to name constants in all capital letters. Access doesn't require this, but it's a nice convention. If you adopt a convention, use it consistently. Your code will appear more professional and be easier for others to follow.

In programming, an unexpected change in the value of a variable is a common cause of problems. A constant can never be changed accidentally by you or any other programmer. Constants are reliable values. Use them as much as possible. When you have been programming for awhile, you will learn to appreciate reliability.

Global Variables

Global variables are defined using the Global keyword as the first word in the declaration. A Global variable is a variable that can be used from anywhere in your program. Here's the syntax for a Global variable:

Global GlobalVarName As DataType

Replace GlobalVarName with the name for your variable and replace DataType with the type of the Global data. The value of a Global variable can be changed from anywhere in your program. For this reason, you should limit the number of Global variables you use.

If you think about it for a moment, limiting the number of Global variables makes sense. When you write code, you make assumptions about the value of variables as you use them. If you are using a Global variable, the value can be modified by another block of code. The end result is that assumptions you make when writing your code can be invalidated by another fragment of code modifying the Global variable. Maintaining control over your data in a program is critical to reducing the number of potential defects that can be induced.

  • + Share This
  • 🔖 Save To Your Account