Word Document Properties

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

---

Listing 9-5
Creating and Using a Simple Collection
‘ Declare the collection.
Private MyCollection As Collection
Private Sub btnAdd_Click()
‘ Add a new item.
MyCollection.Add _
InputBox(“Type a new item.”, “Add Item”, “Hello”)
‘ List the items.
ListItems
End Sub
Private Sub btnDelete_Click()
‘ Define variables to hold the selection.
Dim UserInput As String
Dim Selection As Integer
‘ Define an error handling result variable.
Dim Result As VbMsgBoxResult
‘ Get the input from the user.
RetryInput:
UserInput = InputBox(“Type an existing item number.”,
_
“Remove Item”, _
“1”)
‘ Validate the input.
If Val(UserInput) > 0 And _
Val(UserInput) < MyCollection.Count + 1 Then
‘ Use good input to delete a value.
Selection = CInt(UserInput)
Else
‘ Display an error message.
Result = MsgBox(“Type a number greater than 1 “ +
_
“and less than or equal to the “ +
_
“number of elements.”, _
vbExclamation Or vbRetryCancel, _
“Input Error”)
‘ Allow for a retry.
If Result = vbRetry Then
GoTo RetryInput
Else
Exit Sub
214
Part III: Expanding Your VBA Horizons 
15_046500 ch09.qxp 12/5/06 5:36 PM Page 214

---

End If
End If
‘ Delete the existing item.
MyCollection.Remove Selection
‘ List the items.
ListItems
End Sub
Private Sub UserForm_Initialize()
‘ Initialize the collection.
Set MyCollection = New Collection
End Sub
Public Sub ListItems()
‘ Create the listing variable.
Dim Element As Variant
‘ Clear the current list.
lblCollection.Caption = “”
‘ Display each element in turn.
For Each Element In MyCollection
lblCollection.Caption = lblCollection.Caption + _
Element + vbCrLf
Next
‘ Determine whether to enable the Delete button.
If MyCollection.Count > 0 Then
btnDelete.Enabled = True
Else
btnDelete.Enabled = False
End If
End Sub
This is a standalone form program. The code declares 
MyCollection
as a
private global variable so that all the sub-procedures can access it. The
UserForm_Initialize
sub-procedure initializes the collection.
The example provides two 
CommandButtons
for changing the collection:
Add and Delete. The Delete button is disabled at program startup because
you can’t delete a non-existent element from the collection without produc-
ing an error. Consequently, the first button that you click is Add. The
btnAdd_Click
event handler displays an 
InputBox
that requests string
input. After making the addition by using the 
Add
method, the code calls the
ListItems
sub-procedure.
215