Working with Arrays

In previous chapters you worked with many VBA procedures that used variables to hold specific information about an object, property, or value. For each single value you wanted your procedure to manipulate, you declared a variable. But what if you have a series of values? If you had to write a VBA procedure to deal with larger amounts of data, you would have to create enough variables to handle all of the data.

Can you imagine the nightmare of storing currency exchange rates for all the countries in the world in your program? To create a table to hold the necessary data, you'd need at least three variables for each country: country name, currency name, and exchange rate. Fortunately, Visual Basic has a way to get around this problem. By clustering the related variables together, your VBA procedures can manage a large amount of data with ease. In this section, you'll learn how to manipulate lists and tables of data with arrays.

In Visual Basic an array is a special type of variable that represents a group of similar values that are of the same data type (String, Integer, Currency, Date, etc.). The two most common types of arrays are one-dimensional arrays (lists) and two-dimensional arrays (tables).

A one-dimensional array is sometimes referred to as a list. A shopping list, a list of the days of the week, or an employee list are examples of one-dimensional arrays. A one-dimensional array is simply a numbered list. Each value in the list has an index. Below is a diagram of a list that contains six elements (items):

Notice that the column representing the one-dimensional array is currently empty. If you want to fill this array with data, instead of six individual labels, simply use one variable name followed by a number in parentheses. In the diagram above, item is a variable name and the numbers in parentheses — (1), (2), (3), (4), (5), and (6) — identify individual elements of the array.

All elements of the array must be of the same data type. In other words, one array cannot store both strings and integers. Below are two examples of one-dimensional arrays: a one-dimensional array, cities, is populated with text

Introduction to Access 2003 VBA Programming

(String data type, $), and a one-dimensional array, lotto, contains six lottery numbers (Integer data type, %).

One-dimensional array named cities$ (of String data type)

One-dimensional array named lotto% (of Integer data type)

One-dimensional array named cities$ (of String data type)

One-dimensional array named lotto% (of Integer data type)

cities(1)

Baltimore

lotto(1)

25

cities(2)

Atlanta

lotto(2)

4

cities(3)

Boston

lotto(3)

31

cities(4)

Washington

lotto(4)

22

cities(5)

New York

lotto(5)

11

cities(6)

Trenton

lotto(6)

5

As you can see, the contents assigned to each array element match the variable type. If you want to store values of different data types in the same array, you must declare the array as Variant.

Two-dimensional arrays are tables of data represented in rows and columns. The position of each element in a table is determined by its row and column number. Below is a diagram of an empty two-dimensional array.

^columns

rows ^

1

2

3

1

(1,1)

(1,2)

(1,3)

2

(2,1)

(2, 2)

(2, 3)

3

(3,1)

(3, 2)

(3, 3)

4

(4,1)

(4, 2)

(4,3)

5

(5, 1)

(5, 2)

(5, 3)

Notice how items in a two-dimensional array are identified with row and column indexes. In this diagram, the first element of the array is located in the first row and the first column (1, 1). The last element of the array is positioned in the fifth row and the third column (5, 3). Let's now populate this array with some values. The two-dimensional array below stores the name of the country or region, its currency, and the U.S. dollar equivalent.

Two-dimensional array named exchange (of Variant data type)

Japan

Japanese Yen

102.76

(1,1)

(1,2)

(1,3)

Australia

Australian Dollar

1.29083

(2,1)

(2, 2)

(2, 3)

Canada

Canadian Dollar

1.20892

(3,1)

(3, 2)

(3, 3)

Norway

Norwegian Krone

6.09506

(4,1)

(4,2)

(4, 3)

Europe

Euro

0.744734

(5,1)

(5, 2)

(5, 3)

Although VBA arrays can have up to 60 dimensions, most people find it difficult to picture dimensions beyond 3D. A three-dimensional array is a collection of tables where each table has the same number of rows and columns.

Part I

Each element of a three-dimensional array is identified by three pieces of data: row, column, and table.

0 0

Post a comment