Best ways to search an array/collection for an element

[deltaquattro]

Hi guys!

I'm creating a small library of utilities for VBA programming, and
right now I'm completing my ArrayManagement and CollectionManagement
module. I was considering different solutions for the classical case
of searchin an array or collection for a specified elements. So far
I've considered using Worksheetfunction.VLOOKUP,
Worksheetfunction.Match (needs some care to take into account the
"element not found" case) for the arrays. I also developed an handy
binary search function for sorted arrays, but it works only for 1D
numeric arrays (sorted in either nonincreasing or nondecreasing order)
but I have no idea on how to extend it multiD/non numeric arrays (if
that's possible at all). Also, I would like to find some smart "Is
Nothing"-based trick for searching in a collection. and Any
suggestion/alternatives? Thanks,

Best Regards

deltaquattro

ps if anybody is interested, I can post my "rough" attempts at a
solution.

 

[Bernie Deitrick]

For 2D non-numeric arrays, try either a dictionary object - great if you are
loading a large file. Lots of samples available.

HTH,
Bernie
MS Excel MVP

 

[deltaquattro]

Hi Bernie,

I'll try it - what's a dictionary? Couldn't find it in the book by
Walkenbach.

Thnx,

Best Regards

Sergio

 

[Rick Rothstein]

The Dictionary object is like a Collection object on steroids. You can do a
Google search for...

vb "dictionary object"

to find out more, but here is a link to get you started...

http://support.microsoft.com/kb/187234

--
Rick (MVP - Excel)


Hi Bernie,

I'll try it - what's a dictionary? Couldn't find it in the book by
Walkenbach.

Thnx,

Best Regards

Sergio

 

[Chip Pearson]

I'll try it - what's a dictionary?

It is similar to a Collection object but has some additional and very
useful features, as well as being considerably faster. With a
dictionary, you can read the collection of keys, for example,
something you can't do with a Collection. Also, there is an Exists
property to indicate whether an keyed item exists, so you don't have
to rely on an error code to test if an item exists.

The Dictionary object is defined in the Microsoft Scripting Runtime,
so you'll need a reference to that. In VBA, go to the Tools menu,
choose References, and scroll down to Microsoft Scripting Runtime and
check that item.

I have some helper functions for both Collections and Dictionaries at
http://www.cpearson.com/Excel/CollectionsAndDictionaries.htm .

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]

 

[Chip Pearson]

I have a library of functions related to array available on my web
site. There are about 40 or so functions related to various tasks done
with arrays. You can download a bas file from the page. It might give
some ideas, and you might well have some improvements to my code
(comments welcome). See http://www.cpearson.com/Excel/VBAArrays.htm .

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]

 

[deltaquattro]

Ciao, Chip,

thanks very much for the information and for the link. It would take
some time for me to rewrite my code in terms of dictionaries (BTW, I'm
already rewriting it by eliminating UDTs in favour of classes and
that's a huge improvement, thanks for the tip but thanks to the
code on the page you posted such as for example the ArrayToDictionary
subroutine, I won't need to do that. That's great! If you plan to
write a book about Excel VBA programming, I'm definitely buying it

Best Regards

Sergio Rossi

I'll try it - what's a dictionary?

It is similar to a Collection object but has some additional and very
useful features, as well as being considerably faster. With a
dictionary, you can read the collection of keys, for example,
something you can't do with a Collection. Also, there is an Exists
property to indicate whether an keyed item exists, so you don't have
to rely on an error code to test if an item exists.

The Dictionary object is defined in the Microsoft Scripting Runtime,
so  you'll need a reference to that. In VBA, go to the Tools menu,
choose References, and scroll down to Microsoft Scripting Runtime and
check that item.

I have some helper functions for both Collections and Dictionaries athttp://www.cpearson.com/Excel/CollectionsAndDictionaries.htm.  

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLCwww.cpearson.com
[email on web site]

Hi Bernie,

I'll try it - what's a dictionary? Couldn't find it in the book by
Walkenbach.

Best Regards

 

[deltaquattro]

Hi, Chip,

I downloaded that also and I'm actively using it. If I add code I'll
be happy to send it to you: probably it won't be as error-safe as your
subroutines, which check for any possible error, but maybe you could
find them interesting as starting points to develop safer code.
Thanks,

Best Regards

Sergio Rossi

I have a library of functions related to array available on my web
site. There are about 40 or so functions related to various tasks done
with arrays. You can download a bas file from the page. It might give
some ideas, and you might well have some improvements to my code
(comments welcome). Seehttp://www.cpearson.com/Excel/VBAArrays.htm.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLCwww.cpearson.com
[email on web site]

Hi guys!

I'm creating a small library of utilities for VBA programming, and
right now I'm completing my ArrayManagement and CollectionManagement
module. I was considering different solutions for the classical case
of searchin an array or collection for a specified elements. So far
I've considered using Worksheetfunction.VLOOKUP,
Worksheetfunction.Match (needs some care to take into account the
"element not found" case) for the arrays. I also developed an handy
binary search function for sorted arrays, but it works only for 1D
numeric arrays (sorted in either nonincreasing or nondecreasing order)
but I have no idea on how to extend it multiD/non numeric arrays (if
that's possible at all). Also, I would like to find some smart "Is
Nothing"-based trick for searching in a collection. and  Any
suggestion/alternatives? Thanks,

Best Regards

ps if anybody is interested, I can post my "rough" attempts at a
solution.

 

[deltaquattro]

Ciao, Rick,

thanks for the links, I've checked them out and it looks like I'd
better give up using arrays in favour of dictionaries. I wonder if
there could be some overhead in using dictionaries instead than arrays
coupled with a FindElementInArray function: I have no idea because
I've never heard of dictionaries until now. What do you think? Would
it make sense to always use a dictionary when I need a resizable data
structure which must be often searched for a particular element?

I'm surprised I've never heard of similar structures in other
(admittedly older) languages. Is this maybe characteristic of Object
Oriented Programming? Or it's just me being ignorant?

Best Regards

Sergio Rossi