Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best ways to search an array/collection for an element
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best ways to search an array/collection for an element
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" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best ways to search an array/collection for an element
Hi Bernie,
I'll try it - what's a dictionary? Couldn't find it in the book by Walkenbach. Thnx, Best Regards Sergio On 12 Feb, 18:14, "Bernie Deitrick" <deitbe @ consumer dot org wrote: 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" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best ways to search an array/collection for an element
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) "deltaquattro" wrote in message ... Hi Bernie, I'll try it - what's a dictionary? Couldn't find it in the book by Walkenbach. Thnx, Best Regards Sergio On 12 Feb, 18:14, "Bernie Deitrick" <deitbe @ consumer dot org wrote: 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" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best ways to search an array/collection for an element
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/Collec...ctionaries.htm . Cordially, Chip Pearson Microsoft MVP 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com [email on web site] On Fri, 12 Feb 2010 09:45:26 -0800 (PST), deltaquattro wrote: Hi Bernie, I'll try it - what's a dictionary? Couldn't find it in the book by Walkenbach. Thnx, Best Regards Sergio On 12 Feb, 18:14, "Bernie Deitrick" <deitbe @ consumer dot org wrote: 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" wrote in message ... 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best ways to search an array/collection for an element
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] On Fri, 12 Feb 2010 08:53:03 -0800 (PST), deltaquattro wrote: 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best ways to search an array/collection for an element
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 On 12 Feb, 19:46, Chip Pearson wrote: 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] On Fri, 12 Feb 2010 09:45:26 -0800 (PST), deltaquattro wrote: Hi Bernie, I'll try it - what's a dictionary? Couldn't find it in the book by Walkenbach. Thnx, Best Regards Sergio On 12 Feb, 18:14, "Bernie Deitrick" <deitbe @ consumer dot org wrote: 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" wrote in message .... 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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best ways to search an array/collection for an element
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 On 12 Feb, 19:48, Chip Pearson wrote: 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] On Fri, 12 Feb 2010 08:53:03 -0800 (PST), deltaquattro wrote: 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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best ways to search an array/collection for an element
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 On 12 Feb, 19:04, "Rick Rothstein" wrote: 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) "deltaquattro" wrote in message ... Hi Bernie, I'll try it - what's a dictionary? Couldn't find it in the book by Walkenbach. Thnx, Best Regards Sergio On 12 Feb, 18:14, "Bernie Deitrick" <deitbe @ consumer dot org wrote: 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" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Matching/Finding/Search Array Element | Excel Programming | |||
Rules for element-by-element product in array multiplication | Excel Programming | |||
delete element is collection | Excel Programming | |||
Search array and return element No | Excel Worksheet Functions | |||
Array problem: Key words-Variant Array, single-element, type mismatch error | Excel Programming |