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 actually 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 elements 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 being one cubbyhole of information):

Dim shipOptions(5) As String

After VBA executes the above 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, as follows:

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

Because each of those array elements is actually 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 follows:


= "USPS Media"


= "USPS Priority"


= "UPS Ground"


= "UPS Second Day"


= "UPS Overnight"

The shipOptions array is a one-dimensional array in that it only has 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 variable names (cubbyholes) creates by that VBA statement as follows:

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

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

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

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

Was this article helpful?

0 0

Post a comment