VBA Code Using Arrays To Be Optimized

[NeedForExcel]

Hi,

On the attached file, when a Value from Cell D1 is selected, it outputs corresponding values from Column E separated by commas..

All working as desired with this code -

Please Login or Register  to view this content.

I would like to know If the code can be further optimized? For example better use of arrays, loops etc.

[TMS]

Given that the objective of the exercise is to play with arrays, probably not. In practice, as the desire is to produce a comma separated string, you might as well build a string from the outset.

Regards, TMS

[AlphaFrog]

On small suggestion; this does one Redim Preserve instead of one for each match.

Please Login or Register  to view this content.

[Kyle123]

Use a dictionary, don't redim preserve. It's nasty since it copies the entire array each time you want to add to it

[cyiangou]

Notwithstanding all the comments about redim preserve, here's some points and code you can try:

ConcatCodes.xlsm

Your example seems to imply that you only need to do a single code search at a time. For this, your code looks quite efficient as it is. I'd just make it friendlier for normal spreadsheet use by turning it into a user defined function.

Please Login or Register  to view this content.

With this you don't need a 'firing' mechanism to run the macro, ie. your Worksheet_Change code. You can just enter this formula in E1:

Please Login or Register  to view this content.

But if you really are looking for speed optimisation, then the one logical improvement I'd apply is to do all the codes in one pass. In theory, this code should be a speedup, but your testing may prove me wrong. The procedure outputs a 2-column table of results using TargetCell as the top-left corner.

Please Login or Register  to view this content.

Called like this:

Please Login or Register  to view this content.

Illustration:
ConcatCodes.jpg

[NeedForExcel]

@TMS - Thank you for the suggestion..

@AlphaFrog - You are right with the Redim thing.. I should have thought of it myself..

@Kyle - How can this be done using dictionaries? I am trying, but I am unable to get it working..

[NeedForExcel]

@cyiangou - Thank you for the input..

Actually the thing is, I am learning how to use Arrays effectively along with Code Optimization..

Hence, I'v posted this code, so that the members can guide me where I can further improve..

[NeedForExcel]

Hey Kyle,

I got it working using Scripting.Dictionary

Please Login or Register  to view this content.

Does this look fine? The only issues I am facing is the Array limit of 65k rows for Transpose

[Kyle123]

Having re-read your question, why not simply?:

Please Login or Register  to view this content.

[cyiangou]

benchtests.xlsm

I reconfigured your original code into a benchmarkable format, and ran it 300 000 times x 5 separate runs, and got these times in seconds:
1.652344, 1.664063, 1.640625, 1.640625, 1.632813
That's pretty fast, told you so.

But we are optimizing, so I tested the following changes:

• Changed NewVal to string array
• Dimensioned it only once, to the maximum number of codes possible (ie. # of rows in source table)
• Only do one Redim Preserve at the end (just to get it to the right length for the Join function).

Again, I ran it 300 000 times x 5 separate runs, and got these times in seconds:
1.375, 1.378906, 1.40625, 1.394531, 1.433594

I also tested your last Dictionary based Sub, I ran it 300 000 times x 2 separate runs, and got these times in seconds:
25.30469, 27.78906

[Kyle123]

Your tests aren't equal.

On the arrays, you're populating R outside the loop, for the dictionary, you're doing it inside.

Move it outside the loop and the times are much closer.

Using your test, building the string was around 5x faster than using arrays on my machine:

Please Login or Register  to view this content.

[TMS]

@Kyle; that was my point in post #2.

Regards, TMS

[Kyle123]

Sorry Trevor, missed that

[cyiangou]

@Kyle123

Shucks, I missed that in the Dictionary version; I added that one just before making my post. But that is the only one populating R in the loop.

But for the first two, the difference in array initialization is deliberate. That's the optimization - the design of Conc2 does not require that the array/dictionary be initialized for every distinct calculation. The same array just gets reused.

If I was optimizing one of my apps, that would be a significant consideration. One setup, many batches. If you were instantiating a class, you initialize the class instance, then use its methods. I usually don't care much whether the setup is optimized or not; it's usually a tiny fraction of total run time.

[cyiangou]

Basic rule of optimisation: Remove unnecessary operations from your inner loops.

[Kyle123]

It depends on how much data you have. At some point it becomes more efficient to sort and binary search rather than simply loop the data

[cyiangou]

True. I have been thinking about a recursive binary attack, but I would avoid sorting.

[Kyle123]

Why would you do a binary search without sorting?

[cyiangou]

Not quite a binary search. I'm attempting it now, better to post than explain.

[cyiangou]

Ok, that was interesting. The recursive, divide and conquer method. Not very fast though.

Please Login or Register  to view this content.

This is a similar technique to Quicksort if memory serves. But where Quicksort's goal is to sort, ours is to collect codes. Turns out this is a silly way to do it, but I'm glad I've tested it. Sometimes a different approach has an unexpected performance payoff, especially when recursion's involved.

[Kyle123]

That's very over engineered imo. Taking this back to the original question, there are two things to consider in improving the algorithm. The string method is the fastest, but there are 2 obvious ways to improve it depending on circumstance and how far you'd need to take it.

1. If you need to look up multiple indexes at once, there comes a point where it's worth sorting the indexes. This allows for faster searching (binary), you don't need to iterate all the elements to get the full result. The catch is that the sorting takes time so is likely only worth it if you have a large amount of data and need to do multiple lookups.

2. String building in vba/vb6 is inneficient since the string is recreated in its entirety on each operation, you can google string building class vb6 for a lower level more efficient implementation. Again for short strings the difference just won't be worth the effort, so this is really only worth doing if you expect the result string to be made up of a huge number of elements

In summary, as tms said, you're unlikely to get any faster than looking up and buding the string whilst looping through. Whether it's worth the effort improving the algorithm very much depends on the circumstances, but unless you have a huge amount of data, I really wouldn't bother.

To put this into context, with the sample data, the string building method executes 300,000 iterations in a third of a second.

[cyiangou]

Yep, that's what I also concluded from my practical experiment. Although perfect, I don't consider 20/20 hindsight as a useful skill in developing innovative solutions.

[NeedForExcel]

This thread was amazingly helpful to me..

So basically I tried to measure the Run Time myself too..

All 4 Codes do almost similar Run-Times, but these 2 (UsingArray & KyleLoops) are almost instantaneous..

I used only 50K rows (Which isn't too much, but as much as I would normally require), so that Transpose wont cause issues.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.