Excel VBA 24-Hour Trainer, 2nd Edition

Lesson 12Working 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. After 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 are 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 library at home of perhaps 100 movies. Among those 100 movies are 5 that are your favorite classics. You can declare a variable named myFavoriteMovies, and create a String array with this macro:

Sub FavoriteMovies() Dim myFavoriteMovies(1 to 5) as String myFavoriteMovies (1) = "Gone With The Wind" myFavoriteMovies (2) = "Casablanca" myFavoriteMovies (3) = "Citizen Kane" myFavoriteMovies (4) ...