Word Document Properties

Chapter 9: Working with Arrays and Collections
15_046500 ch09.qxp 12/5/06 5:36 PM Page 211

---

Listing 9-4
Copying an Array
Public Sub ArrayCopy()
‘ Create a loop variable.
Dim Counter As Integer
‘ Create an original array of strings and a copy.
Dim OriginalArray(4) As String
Dim CopiedArray(5) As String
‘ Create an output variable.
Dim Output As String
‘ Fill the array with data.
OriginalArray(0) = “This”
OriginalArray(1) = “is”
OriginalArray(2) = “the”
OriginalArray(3) = “original”
OriginalArray(4) = “array!”
‘ Copy the data.
For Counter = 0 To UBound(OriginalArray)
CopiedArray(Counter) = OriginalArray(Counter)
Next
‘ Modify some data elements.
CopiedArray(3) = “copied”
CopiedArray(4) = “array”
‘ Add a new element.
CopiedArray(5) = “too!”
‘ Create the first part of the output string.
Output = “The first string:” + vbCrLf
For Counter = 0 To UBound(OriginalArray)
Output = Output + OriginalArray(Counter) + “ “
Next
‘ Create the second part of the output string.
Output = Output + vbCrLf + “The Second String:” +
vbCrLf
For Counter = 0 To UBound(CopiedArray)
Output = Output + CopiedArray(Counter) + “ “
Next
‘ Display the results.
MsgBox Output, vbInformation Or vbOKOnly, “Results”
End Sub
The code begins by creating two arrays and filling the original array with
information. Copying the 
OriginalArray
elements to the 
CopiedArray
212
Part III: Expanding Your VBA Horizons 
15_046500 ch09.qxp 12/5/06 5:36 PM Page 212

---

elements comes next. Follow this process in the Debugger, and you see that
the two arrays are the same except that the 
CopiedArray
has one extra ele-
ment. Notice how the code uses the 
UBound
function to determine the last
element of the array. Use the 
LBound
function to determine the lower bound-
ary of the array subscripts.
At this point, the code modifies 
CopiedArray
. First, the code changes the
content of two of the elements. Second, it adds information to the last ele-
ment. These changes don’t affect the original array. If an error occurs, you
can always reconstruct 
CopiedArray
by using 
OriginalArray
as a start-
ing point. The message box that the code constructs shows that the original
array is the same, but the copied array is different.
Using Collections to Create Data Sets
You can view a collection as an advanced form of an array. Like an array, a
collection
maintains a list of items in one package. Because these items are
related in more than a superficial way, such as a group of worksheets, many
people refer to the list of items as a 
data set.
Using a collection is different
from an array, however, and you might find that you like using them better
than arrays. A collection has some advantages, such as not requiring the
ReDim
statement, but is a little more complicated to use. This section explains
these differences in detail and shows how to use collections in a program.
Understanding collection usage
If you’ve followed the book to this point, you’ve used collections in previous
chapters. For example, the “Recovering from an error” section of Chapter 6
relies on a collection to retrieve information from a specific hard drive. The
FileSystemObject
object contains a collection of 
Drive
objects. The
example in the “Designing a form for your application” section of Chapter 7
also relies on collections. This example adds control objects to the 
Controls
collection of the 
UserForm
object. VBA uses a lot of collections — you can’t
escape their use.
The easiest way to understand a collection is to create one of your own. You
can create collections and add them to a class that you create or use them by
themselves. VBA doesn’t place any restrictions on how you use collections.
Listing 9-5 shows an example of a simple collection. It creates the collection
and then provides the means for adding, removing, and listing elements in the
collection. (You can find the source code for this example on the Dummies.com
site at 
http://www.dummies.com/go/vbafd5e
.)
213