Results 1 to 7 of 7
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Enumerating Collections

    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):
    [img]/w3timages/icons/thinks.gif[/img]
    -MarkJ-

  2. #2
    Scott A
    Guest

    Re: Enumerating Collections

    I've heard that using For...Each...Next is more efficient for collections than using For...Next loops. But then you have that Variant in the For...Each loop. I'd say test it. Ken Getz has a little class that uses Windows Timers (API calls) to gather performance data. I think you can download it at http://www.mcwtech.com/. If you can't find it, I put together a similar class, I'll just have to find it [img]/w3timages/icons/smile.gif[/img]

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Enumerating Collections

    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.[img]/w3timages/icons/grin.gif[/img] 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.
    Charlotte

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Enumerating Collections

    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)
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Enumerating Collections

    Thank you all for your most helpful suggestions.[img]/w3timages/icons/clever.gif[/img]

    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

    Again, many thanks! [img]/w3timages/icons/thinks.gif[/img]

    -MarkJ-

  6. #6
    Scott A
    Guest

    Re: Enumerating Collections

    Oh, I wasn't questioning the use of the Variant. I just don't like the possible overhead of it, since it keeps getting sub-typed (for lack of a better word). I fully understand why it is needed and unavoidable when using the Collection object. I'd be curious to know what the performance difference would be between using a Variant to loop through a Typed collection (such as the DAO Tabledefs collection) vs. using the actual data type.
    In other words, compare:
    Dim varLoop As Variant

    For Each varLoop in CurrentDb.Tabledefs
    ...
    Next varLoop
    With:
    Dim tdfLoop As DAO.Tabledef

    For Each tdfLoop in CurrentDb.Tabledefs
    ...
    Next tdfLoop
    With the Collection object it could be even worse because the items in the Collection don't all have to be the same data type.
    Consider this:
    Dim dbTemp As DAO.Database
    Dim varLoop As Variant
    Dim colTest As New Collection ' bad code, but just an example

    Set dbTemp = CurrentDb
    colTest.Add dbTemp.Tabledefs(0)
    colTest.Add dbTemp.Querydefs(0)
    colTest.Add dbTemp.Containers(0)
    colTest.Add dbTemp.Properties(0)

    For Each varLoop In colTest
    ...
    Next varLoop
    Obviously in this example, the only thing you can use is a Variant to loop through the Collection. But, image the overhead involved with sub-typing the Variant for the different datatypes. I'm sure with the computers today, you'd never notice a performance problem. But, it's still fun to think about.[img]/w3timages/icons/clever.gif[/img]

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Enumerating Collections

    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.
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •