|
What are Arrays?
Rodney POWELL
Microsoft MVP - Excel |
|
An array is a group of variables,
usually of the same data type, arranged contiguously in memory. These
variables share a common name. Each variable within an array is called
an element and you use a number (an index) to tell them apart.
An index simply means an identifying number.
In VBA there are two categories of arrays:
- fixed-size array - always remains the same size
- dynamic array - size can change at run-time
For now, we'll focus on the fixed-size array. You declare a fixed-size
array just as you would a regular variable, except that you also need to
specify the number of elements in the array. This is done in parentheses
following the name of the array as you declare it. For example:
Dim MyOneDimensionalArray(10)
or
Dim MyTwoDimensionalArray(10,10)
In these examples, only the upper bound of the arrays have been
specified. The default lower bound is 0 (for a zero-based array).
However you can change the default lower bound so the index for the
first element will be 1 by placing an Option Base 1 statement
in the General Declarations section.
Another way to specify a lower bound is to provide it explicitly
using the To keyword. Here is an example of explicitly
declaring the upper and lower bounds for a 50 element array beginning
after an index of 100:
Dim MyArray(101 to 150)
A one-dimensional array is useful for storing a series of values.
It may help to think of it as a list. For example, you may declare
an array of 7 string variables so that each element corresponds to
the name of a different day of the week.
Sub OneDimensionalArray()
Dim a&, aDayOfWeek$(7)
' Initialize elements of one-dimensional array
aDayOfWeek$(1) = "Monday"
aDayOfWeek$(2) = "Tuesday"
aDayOfWeek$(3) = "Wednesday"
aDayOfWeek$(4) = "Thursday"
aDayOfWeek$(5) = "Friday"
aDayOfWeek$(6) = "Saturday"
aDayOfWeek$(7) = "Sunday"
' Loop through the array and display each of
' it's elements
For a& = 1 To 7
MsgBox aDayOfWeek$(a&)
Next a&
End Sub
A two-dimensional array is similar to a table in a database.
One dimension represents the number of rows in the table, while the
other specifies how many columns (fields) you are defining.
Therefore, each element in a two-dimensional array can be referred
to by its X and Y coordinates. You can efficiently process a
multidimensional array by using nested loops.
Sub TwoDimensionalArray()
Dim c&, r&, Counter&, aMatrix&(3, 3)
For c& = 1 To 3
For r& = 1 To 3
' Increment a counter variable
Counter& = Counter& + 1
' Populate an element of the array
' with counter value
aMatrix&(r&, c&) = Counter&
Next r&
Next c&
End Sub
A three-dimensional array is like a cube. Most arrays are only one- or
two-dimensional. Although VBA allows you to create arrays of up to
60 dimensions, a need for more than a three-dimensional array would be
rare. And, because VBA allocates memory space for every possible element
in a declared array, regardless of whether or not you actually store a
value in that element, avoid declaring an array any larger than is
necessary. Always remain mindful of the size of your arrays, since they
can have significant memory requirements.
Arrays are so powerful, as they makes it so much easier to work with
groups of variables. This enables you to develop shorter and simpler
procedures, because you can use loops that deal efficiently with your
variables.
Know an even better way?
Let me know.
|
|
|
|
![Microsoft Office : Excel - Visual Basic for Applications [ VBA ] Software](img/1ptrans.gif){kind=small}
