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.

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.

--
Regards,
Tom Ogilvy


"Ray Pixley" wrote:

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

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?


"Tom Ogilvy" wrote:

> 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.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Ray Pixley" wrote:
>
> > 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.

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

"Tom Ogilvy" wrote:

> 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.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Ray Pixley" wrote:
>
> > 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.

Set D = CreateObject("Scripting.Dictionary")

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

Ray Pixley wrote:
>
> 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?
>
> "Tom Ogilvy" wrote:
>
> > 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.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Ray Pixley" wrote:
> >
> > > 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.

--

Dave Peterson

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

http://groups.google.co.uk/groups?as...=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

"Ray Pixley" <(E-Mail Removed)> wrote in message
news:79CB9A33-8B69-4D56-8CB2-(E-Mail Removed)...
> 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.

>> 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")

--
Dana DeLouis

"Ray Pixley" <(E-Mail Removed)> wrote in message
news:3CB65991-BAD3-436D-97E9-(E-Mail Removed)...
>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?
>
>
> "Tom Ogilvy" wrote:
>
>> 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.
>>
>> --
>> Regards,
>> Tom Ogilvy
>>
>>
>> "Ray Pixley" wrote:
>>
>> > 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.