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 similar to a particular file holding a specific type of information. The variable may just be a single number or a piece of text that the program needs to hold and refer to while it is running. It could also be a whole array of information, almost like a spreadsheet. A spreadsheet has many cells that can hold information, and an array can be set up to have many cells 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, supplying a name for the variable:

Dim variablename [As type]

Variable names must follow these rules:

  • They must begin with a letter.

  • They must contain only letters , numbers , or the underscore character ‚ no spaces!

  • They must not exceed 40 characters .

  • They must not be a reserved word (see the section ‚“Reserved Words ‚½ at the end of this chapter).

The optional As type clause allows you to define the data type of the variable you are declaring. If you omit the type, it defaults to the Variant data type discussed in the next section.

 Dim MyInteger as Integer 

Implicit Declaration

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

 TempVal=6 

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

However, a problem with doing this is that it can lead to subtle errors in your code if you misspell the name of the variable in a later statement. For example, if you refer to it as temval instead of tempval , you know what you mean but VBA does not. 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 stipulate that VBA always generates an error message whenever it encounters a variable not declared. To do this, you'll need to go to the declarations section of the code module. If you look at a module within the VB Editor window, you will see a heading called (General) in the top left of the module window and a heading called (Declarations) in the top right of the module window. Click (Declarations), and you will go straight to the declarations section. Do not worry if it appears that you are not typing into a defined section. Type the following statement. As soon as you type a declaration, a line will automatically appear underneath to show that it is within the declarations section.

 Option Explicit 

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

 Dim TempVal 

If you refer to temval during execution, an error message will be displayed stating that the variable has not been defined.

Note ‚  

Option Explicit works on a per-module basis ‚ it must be placed in the declarations section of every code module you want it to apply to unless you define the variable as a global 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 variable. The scope is local to that procedure. You will often need variables that can be used by several procedures or even the whole application. For these reasons, you can declare a variable at local, module, or global level.

Local Variables

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.

 Dim TempVal 
Static TempVal

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.

 Dim A(3) 
ReDim A(10)
ReDim Preserve A(12)

To use ReDim , you must define the variable initially as an array (see the section ‚“Arrays ‚½ later in this chapter). Dim A(3) creates a small array with 4 elements (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 ReDim and Preserve to keep the data already in there.

Module-Level Variables

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.

 Dim TempVal 

This would be placed in the declarations section of the module instead of an actual procedure on that module.

Global Variables

Global variables are declared in the declarations part of a module with the Global statement, but they can be accessed by any code within the application. Global variables exist and retain their values for the lifetime of the application.

 Global TempVal 

Again, this would be placed in the declarations section of any module. Because you have specified that it is global, it can be accessed for any part of your code.

Name Conflicts and Shadowing

A variable cannot change scope while your code is running. However, you can have a variable with the same name in a different scope or module. You can have a global variable called temp and also a local variable in a procedure 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 global variables can also cause conflicts with procedure names. A procedure (a subroutine) has global scope unless 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.

Static Variables

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 values are not preserved and the memory is released. The next execution reinitializes the variables for the lifetime of the procedure. You should only use local variables to hold values that are being used during that procedure. If you expect to access them from other modules, they need to be global. However, you can use the Static keyword to declare and preserve a local variable:

 Static Temp 

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

 Static Sub Test_Static() 



Excel VBA Macro Programming
Excel VBA Macro Programming
ISBN: 0072231440
EAN: 2147483647
Year: 2004
Pages: 141

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net