Microsoft Office Tutorials and References
In Depth Information
What Arrays Can Do for You
What Arrays can do for you
Arrays are often used for representing data in lists or tables, where each item in the list is of
the same data type. Some examples might be a list of your friends’ names, all of which would
be String data types, or a table of your city’s average daily temperatures by month, all of which
might be Double data types. Arrays offer you the versatility of storing and manipulating data
items through one array variable, which is much more efficient than assigning variables to every
element in the array.
Say you want to count how many Excel workbook filenames reside in a particular folder. You
don’t know how many total files are in that folder, or how many of those total files are Excel files.
With an array doing the job, you won’t need any worksheet cells to store the filenames. Instead,
you can programmatically compile into memory the count of Excel files, and the individual
filenames too, all of which you can retrieve later in your macro if need be.
The previous arrays of movie titles are an example of one-dimensional arrays. In the macro named
FavoriteMoviesRange , the five movies were listed in range A1:A5. VBA regards this as a
onedimensional array because the array elements stand by themselves in a table that is five rows deep
and one column wide.
Many arrays you deal with will have more than one dimension.
Figure 10-1 expands on this list of classic movies by adding a second
column that lists the year each movie was released. This table is
composed of five rows and two columns. A two-dimensional String array
can be created by associating the movie title elements with their
respective year of release elements.
The first item of business is to declare a String type variable for the array. The size of the array is
specified with the variable, to include the span of rows and columns that make up the array. For
example, with five rows and two columns, a variable named Classics is declared with the
statement Dim Classics(1 To 5, 1 To 2) As String . The following macro loops through rows 1 to
5 in column A and rows 1 to 5 in column B. Each value in the array is stored in memory with two
Integer type variables for collecting row and column data. Based on Figure 10-1, the Message Box
will return 1941 because Classics(3, 2) returns the string value of the element that occupies the
location of the array’s third row and second column.
Dim Classics(1 To 5, 1 To 2) As String
Dim intRow As Integer, intColumn As Integer
For intRow = 1 To 5
For intColumn = 1 To 2
Classics(intRow, intColumn) = Cells(intRow, intColumn).Value
MsgBox Classics(3, 2)