Access VBA Macro Programming

procedure is executing. Local variables declared withStaticremain 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 21, 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 ten elements so it has ten pigeonholes or cells to store information. You can also give it

another dimension so 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 Explorer does, then you would need an

array to store all the pathnames as they were found so you could easily find and refer to them

within your program.

Dim A()

ReDim A( 10 )

ReDim Preserve A( 12 )

To useReDim, 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 four elements (0–3), so there are

effectively fourAvariables.ReDim A(10)then makes it an 11-element array but loses all the

data in it.ReDim A(12) Preservemakes a 13-element array but keeps all existing data. Note

all subscripts start at 0 by default.

ReDimis 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 ten

elements. As this fills up, it can be resized usingReDimandPreserveto retain the data

already in it.

Module-Level Variables

Amodule-levelvariable 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.

16 Microsoft Access 2010 VBA Macro Programming