The
ListItems
sub-procedure provides a convenient place to put common
code for the event handlers. You should use this type of sub-procedure
with common code for programs that you create. The code begins by creat-
ing a
Variant
to hold individual collection members. It also sets the
lblCollection.Caption
to
“”
(nothing) in order to erase the current con-
tent from the display. Notice the use of the
For Each...Next
statement to
fill the
lblCollection.Caption
property with new data.
It’s important to check the status of the collection to ensure that the program
enables or disables
btnDelete
as appropriate. The
If...Then
statement
compares the number of
collection items using the
Count
method with 0.
When the
If...Then
statement is true, the collection is empty, and the code
disables
btnDelete
. Otherwise, the code enables
btnDelete
.
The
btnDelete_Click
event handler requires some error handling to
ensure that the program operates as expected. You can add any string
desired to the collection, but you can’t enter just any information during a
deletion because incorrect input causes an error.
216
Part III: Expanding Your VBA Horizons
Knowing when to use a collection
Throughout this chapter, I show examples of
both arrays and collections. The examples
demonstrate that the techniques for using each
storage technique are different, but the results
can be the same in many cases. For example,
you can store strings
equally well in a collection
or an array. You can also access the information
by using similar techniques. It might be difficult
to determine which method to use. In some
cases, it doesn’t matter — it’s a matter of per-
sonal taste.
Consider both advantages and disadvantages
when you make your decision. Here’s a list of
the things that you should consider:
Arrays have a slight performance advan-
tage and use less memory to store the same
information.
Collections are more flexible than arrays —
they can grow and shrink as needed.
Arrays
are easier to conceptualize, and you
might find them easier to use.
Collections excel at storing objects, and
they work well with complex data types.
Arrays can appear in multiple dimensions —
there’s no limit. (Collections are limited to a
single list.)
Collections are self-contained, so you don’t
have to memorize a list of external functions
to work with them.
Although you can use either an array or a col-
lection in some situations, you must use a spe-
cific option in other cases. For example,
arrays
are the only solution when you need to perform
complex matrix math because collections are
simple lists and don’t include the concept of
dimensions. On the other hand, when you want
to include a list of objects within an object, you
must use a collection. However, you can use
either solution when you want to create a list of
simple items, such as names.
15_046500 ch09.qxp 12/5/06 5:36 PM Page 216
The
InputBox
places the information
that it receives in
UserInput
. An
If...
Then
statement compares the
UserInput
with specific numeric values. When
you type a character or a special symbol, it’s the same as typing a 0 — the
Val
function returns a 0 for non-numeric input. The
If...Then
statement
also compares the
UserInput
with the upper limit of the collection entries
by using the
Count
method. If the input meets the criteria, the code places
the integer part of the input into
Select
by using the
CInt
function.
When
an input error occurs, the code displays a message box that tells you
about the correct input and asks whether you want to try again. An
If...
Then
statement compares your button click with
vbRetry
and takes appro-
priate action.
The deletion process relies on the
Remove
method. You must supply an inte-
ger value that reflects the index of the entry that you want to remove. A col-
lection can also use
keys
— strings that stand in for the actual values. You
can see a demonstration of this technique in the upcoming “Adding keyed
data to the collection” section. The event handler ends with a call to
ListItems
.
Adding
keyed data to the collection
You can normally create collections without keys, and they work fine. A col-
lection that relies on user input is an exception. It’s easier to get string input
from users than to ask them to count down a row of entries to provide a
number. Database collections provide opportunities to use keyed entries. In
fact, many predefined collections use keyed entries to make it easier for you
to develop programs with them.
This example shows an Access contact database. It uses keys to make finding
an entry easier. The database contains only three fields: a contact name, the
telephone number, and the last date of contact. To use this example, you must
add a reference to the Microsoft DAO 3.6 Object Library. Listing 9-6
shows the
code for this example. (You can find the source code and associated database
for this example on the Dummies.com site at
http://www.dummies.com/
go/vbafd5e
.)
Do'stlaringiz bilan baham: 
