Microsoft Office Tutorials and References
In Depth Information
Lesson 10: Working with Arrays
Elements in an array are variables, and you can refer to a specific element by its index number inside
the array. Because the array name is myFavoriteMovies , and the Message Box is referring to the
third element in that array, when you run this macro, the Message Box will display Citizen Kane.
You have created an array which is a collection of your favorite classic movies. You can loop through
each element in that collection — that is, each movie title — by referring to its index number inside
the myFavoriteMovies array. The following macro shows how to display each movie title element in a
Message Box.
Sub FavoriteMoviesLoop()
Dim myFavoriteMovies(1 To 5) As String
Dim intCounter As Integer
myFavoriteMovies(1) = “Gone With The Wind”
myFavoriteMovies(2) = “Casablanca”
myFavoriteMovies(3) = “Citizen Kane”
myFavoriteMovies(4) = “Sunset Boulevard“
myFavoriteMovies(5) = “Modern Times“
For intCounter = 1 To 5
MsgBox myFavoriteMovies(intCounter), , _
“Favorite #” & intCounter
Next intCounter
End Sub
If you would like to populate a range of cells with the elements of your array, this macro demonstrates
how to do that, listing the movie titles in range A1:A5.
Sub FavoriteMoviesRange()
Dim myFavoriteMovies(1 To 5) As String
Dim intCounter As Integer
myFavoriteMovies(1) = “Gone With The Wind”
myFavoriteMovies(2) = “Casablanca”
myFavoriteMovies(3) = “Citizen Kane”
myFavoriteMovies(4) = “Sunset Boulevard“
myFavoriteMovies(5) = “Modern Times“
For intCounter = 1 To 5
Cells(intCounter, 1).Value = myFavoriteMovies(intCounter)
Next intCounter
End Sub
VBA regards the array itself as one variable, but inside the array is a group of two or more elements
that you can work with separately. You can, and often will, refer to each element by its index
number, which is its position in the array. This way, you can pick a particular element in the array to
work with based on its index number, or you can loop through all the index numbers one after the
other, in case your project calls for every element to be worked on.
Search JabSto ::




Custom Search