Searching Arrays - Application.Match or loops?

[Rezzy777]

I often try to find a specific value inside an Array.
I have always used regular loops for this, but I recently found out that I can also use Application.Match which to me seems a lot easier to use.
When I suggested this in one of my earlier posts on this forum, I was told Application.Match is probably slower than loops.
I was hoping that really Application.Match would be faster, but I can't seem to find a fitting answer which I should use...

So currently I am at a crossroads: should I start rebuilding my macros with Application.Match or should I just keep my loops?

Just to be clear: I am NOT searching in Worksheets here - I am searching in ARRAYS, and I want to search them as fast as possible.


Many thanks in advance!

[romperstomper]

Why don't you run some timing tests in your code?

[jindon]

How to check an Array without loops

[Rezzy777]

Why don't you run some timing tests in your code?

I was hoping one of those two ways was already publicly known to be the fastest ;-)

[Rezzy777]

How to check an Array without loops

Ah, know I see: I thought you were referring to the Worksheet function "Match" (my bad) ;-)

[romperstomper]

If you need to search your arrays that often, perhaps you should be using some other storage mechanism, like a Dictionary or Collection.

[Rezzy777]

If you need to search your arrays that often, perhaps you should be using some other storage mechanism, like a Dictionary or Collection.

Why is that? Is there any advantage of a Dictionary or Collection over an Array when it comes to searching?

[romperstomper]

You can refer to an item directly using its key instead of searching.

[Rezzy777]

Ah, true! But then I'm limited to only one dimension, right? ;-)

[romperstomper]

Yes. You haven't given any context, so I'm just throwing out ideas.

[Rezzy777]

You are right ;-) I'll remember that when I only need 1 dimension.

So for two dimensions I need an Array and a loop when I need to search fast (instead of Application.Match), right?

[romperstomper]

Possibly. There is no "one size fits all" answer as to what is the best approach. In my opinion, if you're that bothered about the fractions of a second difference in performance, you're probably using the wrong tool anyway.

[torachan]

Give the attached approach a try to see if meets your needs.
it was developed to demonstrate that data accumalation after 10 years could still be sorted at acceptable speeds.
the array is seeded by the initial 20 lines, place a number (10,000 rows takes approx 2 seconds to sort) then 'FILL ARRAY'
the sort is on three criteria -customer code and between two dates - refer to the seed list for the range available.
then press 'SORT' this will produce the other two sheets of sorted data and record the time of the process.
The sort is done by filling the array with "" for those rows not meeting the criteria, then removing those rows containing "", thus leaving the data required.
I have briefly annotated the code to indicate process.
torachan.

[Rezzy777]

Possibly. There is no "one size fits all" answer as to what is the best approach. In my opinion, if you're that bothered about the fractions of a second difference in performance, you're probably using the wrong tool anyway.

Didn't know the difference would be that small (fractions of a second). In that case I could go ahead and use Application.Match after all ;-)

[Rezzy777]

Give the attached approach a try to see if meets your needs.
it was developed to demonstrate that data accumalation after 10 years could still be sorted at acceptable speeds.
the array is seeded by the initial 20 lines, place a number (10,000 rows takes approx 2 seconds to sort) then 'FILL ARRAY'
the sort is on three criteria -customer code and between two dates - refer to the seed list for the range available.
then press 'SORT' this will produce the other two sheets of sorted data and record the time of the process.
The sort is done by filling the array with "" for those rows not meeting the criteria, then removing those rows containing "", thus leaving the data required.
I have briefly annotated the code to indicate process.
torachan.

Wow thanks, I'll give it a try!