Searching an array

Is there a good way to search an array with VBA? My program looks through an excel file and puts values into an array, but only if the values aren't already in the array. I'm determining if the value is already in the array in pretty much the worst way possible: I loop through every entry in the array and check if it is equal to the value I might put at the end of the array.

So yeah, this is really slow because I have multiple excel files and almost 500000 rows I need to check. Is there something built in to VBA that will help me with this or am I going to dust off my Data Structures and Algorithms textbook and write some search and sort algorithms?

Thanks,

Zack

I'm not sure if this would be any quicker but you could use the JOIN function in VBA to make some long strings out of your array and use INSTR to detect if the value is new or not.

I've not found anything like that in VBA.... but I'm not 100% familiar with it. It is a bit of a nuisance not having some of the normal functions I'm used to in other languages like C/C++.

Anyway... if your data isn't sorted, and you don't do frequent lookups, then what you are doing is probably best. ie. O(n). (Sorts are O(n lg n) at best... so sorting it and then searching will be slower then just doing the search)

If your data isn't sorted, and you do a large number of lookups, you might want to figure out a way to sort it and do a binary search. This may involve keeping the list sorted as you add new values. Sorting it every time you do a lookup could be as bad as O(n lg n) which is worse than O(n). Depending on your data setup, you could use a hash table of some kind, then the lookups will be fairly fast.

If your data is sorted, you should probably dust off your book and write yourself a binary search, which will get you O(lg n).

Scott