Understanding 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 names of the week, or an employee list are examples of one-dimensional arrays, or numbered lists. 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 parenthe-ses—(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. The diagrams on the following page are two examples of one-dimensional arrays: a one-dimensional array called cities is populated with text (String data type—$), and a one-dimensional array called lotto contains six lottery numbers (Integer data type—%).

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

cities(1) cities(2) cities(3) cities(4) cities(5) cities(6)

Baltimore

Atlanta

Boston

Washington

New York

Trenton

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

lotto(1) lotto(2) lotto(3) lotto(4) lotto(5) lotto(6)

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.

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 indices. 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 third column (5, 3). Let's now populate this array with some values. The two-dimensional array shown below stores the name of the country, its currency, and the value per U.S. dollar. Two-dimensional array named exchange (of Variant data type):

Japan

Japanese Yen

128.2

(1,1)

(1,2)

(1,3)

Mexico

Mexican Peso

9.423

(2,1)

(2,2)

(2,3)

Canada

Canadian Dollar

1.567

(3,1)

(3,2)

(3,3)

Norway

Norwegian Krone

8.351

(4,1)

(4,2)

(4,3)

Hungary

Hungarian Forint

266.7

(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. Each element of a three-dimensional array is identified by three pieces of data: row, column, and table.

Tip 7-1: What is an Array Variable?

An array is a group of variables that have a common name. While a typical variable can hold only one value, an array variable can store a large number of individual values. You refer to a specific value in the array by using the array name and an index number.

Tip 7-2: Subscripted Variables

The numbers inside the parentheses of the array variables are called subscripts, and each individual variable is called a subscripted variable or element. For example, cities(6) is the sixth subscripted variable (element) of the array cities().

0 0

Post a comment