Curious about why one macro runs so much quicker than another

[bodhi808]

Hi Guys,

From another thread (http://www.excelforum.com/excel-prog...30#post3213330)

My VBA is self taught and doubtless lacks good structure. One thing that I'm particularly curious about is the methods used between a macro that I wrote to solve the problem on the above thread and another written by another member whose code does essentially the same exercise but in a fraction of the time that mine runs.

My code was.

Please Login or Register  to view this content.

The better solution and enormously faster solution was posted by stanleydgromjr as follows.

Please Login or Register  to view this content.

I'm keen to get some help and advice on better coding practice and structure and also to understand why there is such a large difference in the execution time between the macros.

Many thanks for any help and advice.
Chris

[TMS]

Simple answer: Stan reads all the data into one array, processes it in memory creating a second array, and then outputs the second array back to the worksheet.

You are processing individual cells on the worksheet, one by one. This will probably means that a) the screen is updated and b) a calculation is performed every time you change a cell.

Try switch off ScreenUpdating before you start your loop and on again when you finish. And set Calculation to Manual before the loop and Automatic afterwards.

Regards, TMS

[stanleydgromjr]

TMShucks,

Thanks for the explanation.

[TMS]

No problem. Hope I got it right and wasn't jumping in where I wasn't needed ... not sure how often you get on here and if you would have seen this.

Regards, TMS

[s4driver]

TMS or Stan,

would one of you be able to look at another problem I posted yesterday? your expertise would be greatly appreciated. here's the link. http://www.excelforum.com/excel-prog...ze-totals.html s4

[TMS]

@s4driver: seems I missed the moment as the thread has some replies and is marked Solved.

Regards, TMS

[s4driver]

no problem, thanks for checking.

[bodhi808]

Apologies for the late response, and thanks very much guys, that makes a lot of sense. Great explanation TMS.
I have used the screen updating method in the past to speed up a macro, but hadn't thought about the manual calculation scenario.
I have read through Stan's code a number of times but I've yet to fully get my head around it. I will have to find an array for dummies exercise I think. Anybody know of any?
Although I have used very simple arrays in the past, I tend to avoid them due to my lack of understanding, but this has demonstrated just how effective they can be!

Many thanks
Chris

[TMS]

You're welcome. Thanks for the rep.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

[stanleydgromjr]

bodhi808,

I have read through Stan's code a number of times but I've yet to fully get my head around it.

I will add some text lines to the macro to explain how it functions.

Be back in a little while.

[stanleydgromjr]

bodhi808,

I have read through Stan's code a number of times but I've yet to fully get my head around it.

I hope this helps.

See the text lines beginning with '' in the code:

Please Login or Register  to view this content.

[bodhi808]

Stan, thank you so much for doing that, it's fantastic!

It's such an elegant (if VBA can be described that way) solution to the problem and a dynamic approach that I've never understood before. I expect to many seasoned VBA guys it's second nature, but I feel like a light bulb has come on haha.

I hadn't realised that the array can be populated by simply stating a = Range("A2:H" & lr) and conversely written back by to the cells in the same manner. I also thought that the array sizes had to be declared initially rather than declaring the arrays as variant. This really helps me to understand their use in simplifying some of my convoluted coding.

ReDim is something that I haven't come across before so I will read up on that too.
One thing from my basic understanding of arrays is that by default they start from position 0 unless specified, like in your ReDim line for array 'b'.
Does the 'a' array run from 0-7 and not 1-8? I had thought that the Ubound function also counted from position 0?
I assume they mustn't do because the loops cycle through the arrays without missing the last column when they both use 'ii' variable as the position across the array.

Again, thank you very much for taking the time to help me understand this, I really appreciate it and have learned a awful lot from this one thread.
Chris

[stanleydgromjr]

bodhi808,

Thanks for the feedback.

You are very welcome. Glad I could help.

I also thought that the array sizes had to be declared initially rather than declaring the arrays as variant.

An array declared as Variant can hold any type of information, text, numbers as integer, long, double.

You can declare an array As String.

One thing from my basic understanding of arrays is that by default they start from position 0 unless specified, like in your ReDim line for array 'b'.

Because of the way my mind works, I usually define my arrays From 1 to some number.

Please Login or Register  to view this content.

Does the 'a' array run from 0-7 and not 1-8?

In the VBA editor you would have to have the Locals window open, and, when you step thru the code you will see the in the Locals window how this works.

Please Login or Register  to view this content.

I had thought that the Ubound function also counted from position 0?

LBound is the lower boundry of the array element which by design could start at 0 or 1.

UBound is the upper boundry of the array element.

variant array a(1 to 45, 1 to 8)

The 1 to 45 starts/refers to the rows of the array, beginning in LBound of 1 to UBound 45.

The 1 to 8 starts/refers to the columns of the array, beginning in LBound of 1 (column number 1) to UBound 8 (column number 8).

I assume they mustn't do because the loops cycle through the arrays without missing the last column when they both use 'ii' variable as the position across the array.

I use i variable to count/cycle vertically thru the a array.

I use the ii variable to count/cycle horizontally thru the a array.

I use the iii variable to count/cycle thru the b array when writing the rows in the b array.

Please Login or Register  to view this content.

Again, thank you very much for taking the time to help me understand this, I really appreciate it and have learned a awful lot from this one thread.
Chris

Come back anytime.