Microsoft Office Tutorials and References
In Depth Information
Lesson 10: Working with Arrays
Working with arrays
This lesson introduces you to arrays in VBA. As you will see, arrays are a very useful way to
programmatically group and store many items of related data. Once you’ve collected your array of
data items, you can access any of the items individually, or access the group as a whole. Arrays can
help you accomplish various tasks in a logical and efficient manner, which is important to
remember when you find yourself faced with some tasks for which arrays will be the only alternative.
WHAT is An ArrAy?
An array is like a variable on steroids. In addition to being a variable, an array also serves as a
holding container for a group of individual values, called elements , that are of the same data
type. You can populate the array yourself by specifying the known elements in your macro,
or you can let VBA populate the array during the course of the macro if you don’t know how
many elements the array will end up containing.
The concept of arrays can be challenging to grasp at first, so a real-world analogy might help.
Suppose you are a fan of classic movies, and you keep a CD library at home of perhaps 100
movies. Among those 100 movies are five that are your favorite classics. You can declare a
variable named myFavoriteMovies , and create a String array with this macro:
Dim myFavoriteMovies(1 to 5) as String
myFavoriteMovies (1) = “Gone With The Wind”
myFavoriteMovies (2) = “Casablanca”
myFavoriteMovies (3) = “Citizen Kane”
myFavoriteMovies (4) = “Sunset Boulevard“
myFavoriteMovies (5) = “Modern Times“