Storing data in arrays

If you think of a variable or constant as one little cubbyhole in which you can tuck away information, a collection of cubbyholes is an array. Each cubbyhole is an element of the array, although each is just a variable in which you can store information. The cubbyholes in an array, however, all have the same name. You use a subscript in parentheses, which defines an element's position in the array, to refer to a specific item in the array.

Declaring an array is a lot like declaring a single variable, but you have to tell VBA how many items are in the array. The syntax looks like this:

Dim name(dimensions) As type where name is a name that you give the array, dimensions specifies how many items are in the array, and type is one of the data types listed in Table 4-1. For example, the following VBA statement creates an array named shipOptions that contains five elements (each element is one cubbyhole of information):

Dim shipOptions(5) As String

After VBA executes this statement, five little cubbyholes, each capable of storing any text (string), are available. The first array element is named shipOptions(l) (pronounced "shipOptions sub one"). The second element is named shipOptions(2) (pronounced "shipOptions sub two"), and so forth:

shipOptions(l) shipOptions(2) shipOptions(3) shipOptions(4) shipOptions(5)

Because each of those array elements is a string variable, you could assign a value to each by using the same syntax that you use to assign values to individual variables, as shown here:

shipOptions(l) =

"USPS Media"

shipOptions(2) =

"USPS Priority"

shipOptions(3) =

"UPS Ground"

shipOptions(4) =

"UPS Second Day"

shipOptions(5) =

"UPS Overnight"

The shipOptions array is a one-dimensional array because it has only one dimension: length. Each item in the array contains exactly one subscript, indicating the item's position in the one-dimensional list of items. You can also declare multidimensional arrays. For example, a two-dimensional array has two dimensions — length and width — like a table.

The following VBA statement declares a two-dimensional array named miniTable that contains three rows and two columns:

Dim miniTable(3,2) As String

Each element in the two-dimensional name has two subscripts. The first subscript represents the row position of the element. The second subscript represents the column position of the element. Hence, you can envision the following variable names (cubbyholes) created by that VBA statement:

miniTable(1,1) miniTable(1,2)

miniTable(2,1) miniTable(2,2)

miniTable(3,1) miniTable(3,2)

In Access, where you already have tables to store all your data in rows and columns, you rarely need to use multidimensional arrays. However, from the standpoint of modifying existing code, when you see a Dim statement that declares a name followed by a number in parentheses, as in

Dim x(10) As String you need to be aware that the statement is creating ten separate variable names: x(1), x(2), x(3), and so forth, up to x(10).

0 0

Post a comment