Enumerating Collections

Topic

New Reply

Greetings!
I’m currently using Access 2000 to rewrite an application I did several years back. I’ve learned a lot of new techniques since I had written the original.

I’m trying to find ways to make the code more efficient. One of the issues that I’m not quite clear on is how best to enumerate a collection. Note: I have anywhere from 25 to 150 objects in the collections. Here are both methods I’ve used:

Method 1 (original way):

-MarkJ-

Reply | Quote

Replies

The For Each method should be faster because it only references the collection object once, while the other method references it in every loop. Of course, the difference may not be noticeable unless you’re a computer. The variant is there because you don’t know what is in the collection. I sometimes use collections that contain recordsets, and the only way to reference them is by assigning the individual element of the collection to a variant.

Reply | Quote

Would the first method be any faster if written like this?:
Dim MyCollection as New Collection
Dim intCounter as Integer
With MyCollection
For intCounter = 1 to .Count
.Item(intCounter).ForeColor = RGB(0,255,0) ‘or whatever
‘Blah Blah Blah
Next ‘intCounter
End with
I have read that For…Next loops are faster if you don’t specify the counter name after the Next – never speed tested it though (I always include it for clarity but with an apostrophe in front)

Reply | Quote

Thank you all for your most helpful suggestions.

I definitely agree that it’s quicker to use the Integer to loop through large collections rather than the Variant, although there are times when the Variant is necessary.

I found the webpage article by Ken Getz (suggested by Scott A) very informative. Its level of detail far exceeds my current needs, but very helpful nonetheless.
Here’s a link directly to the article:
http://www.microsoft.com/officedev/articles/movs101.htm%5B/url%5D

Again, many thanks!

-MarkJ-

Reply | Quote

I’m not that in love with technicalities! Actually, you wouldn’t normally use the variant in looping through built-in collections anyhow, because you already know by definition what type the members are going to have. But when you use a free-form collection, you don’t have much choice. And of course, if the collection holds recordsets or arrays, you have to use a variant because nothing else works.

When I’m looping through something like a collection of controls, I use the generic control object but when I’m looking for a particular kind of control (i.e., I’m instantiating a custom class and binding that control to it), I subtype it before I try and do anything specific with it.

Reply | Quote