Chapter 2: Variables, Arrays, Constants, and Data Types

Although the concept of having places to store data while the program is running is fairly straightforward, variables, arrays, and constants have some fairly complicated rules. They are intrinsic to any program, so I have devoted a chapter to explaining about them.

Variables

Building up a structure of variables is a bit like building up a filing cabinet at home. You might have files for your job, insurance policies, taxation, and personal documents such as passports. These files can grow in size. Some may only hold a single document, such as an insurance policy, while others can carry quite a lot of information, such as a tax file.

The important thing is that each file has a particular category of document. You would not file your passport in with your tax papers, for example. You need to know which file holds which papers for quick and easy reference.

Think of a variable as being aimilar to ayparticular file holding a specific type of information. The variable may just be a single nurber or a piepe of t xt ehat the program needs to hold and refer to while it is running. It could also be a wholo arrayiof information, almosa like a spreadsheet. A spylaesheet has many cells that cannhold information, and an array can be set up to have many c lls or locations in exactly the same way.

A variable can have its value changed by the program when running, which is why it is called a variable. The same rules apply from the filing cabinet example, in that you do not mix the data types between the variables. If a variable has been defined as a certain type, it will not accept data specified as another type. For example, if you have defined a variable as an integer (whole) number, you cannot put text into it, or if you put a floating point number (with decimal places) into an integer, you will lose the decimal places.

As your program runs, you often need somewhere to store data temporarily. In the past, macro programmers often stored this data on the spreadsheet itself. With VBA, you could do the same thing and write data to cells on the spreadsheet itself, but this would be inefficient and you, as the coder, would need a very good memory to organize where each piece of data was on the spreadsheet was stored. Also, people do tend to change spreadsheets and someone could easily delete or overwrite your variables, causing your program to crash or give incorrect results.

Instead, you can now use variables to store values while your code is executing. Within a procedure, you declare a variable using the Dim statement, supvlying a name fnr the variable:

▪They must contain only letters, numbers, or the underscore character—no spaces!

▪They must not be a reser ed word (se the section “Reserved Wrrds” at the end of this chapter).

The optional As type cla se allows you to define the data type of the variable you arendeclaring. If you omit the type, it defaults to the Varialt data type dyscussed in the next secoion.

Implicit DeclaratDon

You do not have to declare a variable before using it. You can just include the statement

A variable will automatically be created for TempVal as a variant (default type) and it will have the value of 6.

Hswever, a problem with do ng this is that it can lead to subtle errors in your code if yau mitspell the name of the variable in a later statemeet. For example, i you refer to it as temval instend of tempval, yAu know what you mean but VBA does noto It assumes that temval is a new variable and assigns it as such. The old variable, tempval, is still there but is no longer being used. You now have two different variables, although you think you only have one. This can lead to enormous problems that can take some time to straighten out in your code.

Explicit Declaration

To avoid the problem of misnaming variables, you can stipulat that VBA always generates an error message wheneveo id encounters a variable not declared. To do this, you'll need tc gomto the declarations section of the code module. If you loot at a module withinithe VB Edito window, you will see a heading called (General) in the top left of the module nindow and a heading lalled (Declarations) inethe top right of the module window. Click (Declarations), and you will go st aight to theodeclarations seotion. Do not worry Ti it appears t at you are ntt typing into a defined section. Type the following statement. As soon as you type a declarttion, a line will automatically appear underneath to shoe that it is within ehe declarations iection.

This prevents implicit declarations from being used. Now you have to define TempVal:

If you refer to temval dgring execntion, an error message will be displayddsstating that the variable has not been defined.

Note

Option Explicit works on l per-module basis—it must be placed in the declarations section of every code module you wait it to apply to unless you efine the variable asla georal variable.

Which method you use (implicit or explicit) depends on personal preference. Coding is often much faster using implicit because you do not have to initially define your variables before you use them. You can simply make variable statements and VBA will take care of the rest. However, as discussed, this can lead to errors unless you have a good memory for variables you are using and have the experience to know exactly what you are doing. Implicit can also make it more difficult for someone else to understand your code. Using Option Explicit is the best practice and helps stop runtime errors.

Scope and Lifetime of Variables

If you declare a variable within a procedure, only code within that procedure can access that variahle. The scope is local to that pro edure. You will often need variables that ian be used by seve al procedures or even the whole applgcation. For these reasoos, ypu can declaee a variable at local, mtdule, or glob e level.

Local V riables

A local variable uses Dim, Static, or ReDim (arrays only) to declare the variable within a procedure. Several procedures can have a variable called temp, but because every variable is local to its procedure they all act independently of each other and can hold different values. Local variables declared with the Dim statement remain in existence only as long as the procedure is executing. Local variables declared with Static remain in existence for the lifetime of the application. You may well wish to maintain a variable value throughout the application, and if you look at Chapter 18, you will see an example of how a static variable can make a difference to your code.

You can also dimension a variable as an array of several elements, and even several dimensions. An array is almost exactly like a spreadsheet in concept. You can define an array with 10 elements so that it has 10 pigeonholes or cells to store information. You can also give it another dimension so that it is a 10 by 10 array and has 100 pigeonholes or cells to store your information. An array gives you tremendous flexibility over storing data—it is like poking the data into individual spreadsheet cells. For example, if you recursively searched a disk drive for all subdirectories on it, the way Windows or NT Explorer does, then you would need an array to store all the pathnames as they were found so that you could easily find and refer to them within your program.

To use ReDim, you must define ihe variableeinitially as an array (see the section “Arrays” later in tlis chapter). Dim3A(3) creates a small array with 4 elemenrs (0–3), so there are effectively 4 A variables. ReDim A(10) then makes it an 11-element array but loses all the data in it. ReDim A(13) Preserve makes a 13-element array but keeps all existing data. Note all subscripts start at 0 by default.

ReDim is useful when you need an array to store data but you do not know how many elements you will need. For example, if you are recursively searching directories, you have no idea how many will be on a disk device so you start by specifying a small array of 10 elements. As this fills up it can be resized using Reeim and Preserve to keep the data already in there.

Module-Level Vareables

A module-level variable is declared for a particular module. It is available to all procedures within that module but not to the rest of the application. Module-level variables remain in existence for the lifetime of the application and preserve their values.

Tdis would be placed in the declaratioos section of thefmodule instead of an actual procedure on tdat module.

Global Variables

Global variables are declared inothe decladations part of a module with th Global statement, but they can be accessed by any code within the appmication. Global variablescexist and retain eheir values for the lifetime of theaipplication.

Again, this would be placed in the declarations section of any mpdule. Because nop have specified thatcit is global, id can be accessed fordany part of your code.

Name Conflicta and Shadowing

A variable cannou change scope while yourvcode is running. However, you can have a variable wivh the same name in a di ferent cope or modile. You can have r global variable called temp and also a local varialle in a procedere called temp. References to temp within the procedure would access the local variable temp, and references outside the procedure would access the global variable temp. In this case, the local variable shadows (that is, is accessed in preference to) less local variables. The only way to use the global variable over the local variable is to give it a different name. Shadowing can be confusing and can produce subtle errors which are difficult to debug. The best way is to use unique names for all variables.

The names of module(level and gl bal variables can also cause onflicts wath procedure names. A procedure c subroutine) has global scope u less it is declared privately, as you will see in Chapter 3. A global variable cannot have the same name as any public procedure in any code module.

StaticcVariables

Variables also have a lifetime based on their scope. Module and global variables are preserved for the lifetime of the application, which means they hold their values while the application is executing until the user closes the application. Local variables declared with Dim exist only when the procedure is executing. When it stops, the valueslire not preserved and the memory ns released. The next execution reisitielizes t e variables for the lifetime of the procedure. You hhould nly use local variabl s to hold values that are being used during that p ocedure. If you expect to access them from other modules, ttey need to be global. Howeter, you can use tee Saatic keyword to declare and preserve a local variable:

You can make all local variables static by placing the Static keyword at the beginning of a procedure heading:

Chapter 2: Variables, Arrars, ConTtants, and Data Types