How does Collection.Item search

[Guest]

I recently moved from searching for data by arrays to collections, but found
that my macros now take 2 or 3 minute to process whereas when I was using
arrays it took 2 or 3 seconds to do the same work. But I don't want to go
back to arrays because indexing is always alway error prone.

I could add a binary search subroutine to speed up the search, but I was
wondering if that's already included in the Collections class. So, how does
Collections use the key to look up an item? Is it a simple sequential
search? If the adds are made with the keys presented alphabetically (less
work than debugging a binary search subroutine), will it respond better?
Thanks.

 

[Guest]

I suspect it builds a hash table or index

This impression is reinforced from an Article on MSDN about working with
collections:

"This is because after deleting the first item, all items in the collection
are re-indexed so that what was the second item is now the first. "

A dictionary object from the scripting runtime may give you more of what you
need.

 

[Guest]

I saw the docs on the dictionary and tried it, specifically (copying from the
help file) :

Sub testDictionary()
Dim d 'Create a variable
Set d = CreateObject(Scripting.Dictionary)
d.add "a", "Athens" 'Add some keys and items
d.add "b", "Belgrade"
d.add "c", "Cairo"
End Sub

But when I try to execute (F8) it, I get "Compile Error: Variable not
Defined" and the word "Scripting" is not highlighted.

Suggestions?

 

[Guest]

Er....Make that "Scripting" is highlighted.

I suspect it builds a hash table or index

This impression is reinforced from an Article on MSDN about working with
collections:

"This is because after deleting the first item, all items in the collection
are re-indexed so that what was the second item is now the first. "

A dictionary object from the scripting runtime may give you more of what you
need.

 

[Dave Peterson]

Set D = CreateObject("Scripting.Dictionary")

It's just a string here, so surround it with double quotes.

 

[NickHK]

You may find this thread interesting. Whilst it for VB not VBA, the
underlying reasoning applies.

http://groups.google.co.uk/groups?a...007&as_maxd=4&as_maxm=7&as_maxy=2007&safe=off

Note there are other implementations of a "Collection" available that will
may be faster than VBA/VB's built in one.

NickHK

 

[Dana DeLouis]

Set d = CreateObject(Scripting.Dictionary)


Just to let you know, that error in the Help system has been there for a
long time.

Set d = CreateObject("Scripting.Dictionary")