Make code faster/better best practices

Hi,

There is a small time overhead involved every time you jump backwards and forwards to the Excel Applications from the VBE and vice versa. So depending on the number of cells you're processing, it might be better to read all the cell values into a VBA array, process them entirely within the VBE and then finally write the results back to the Excel App.

HTH

Hi and thanks.

Just to clarify, did you mean to read BOTH lists into individual arrays.........and process them using the arrays.

David

Anyone have a good / fast way to compare to arrays ?

thanks SHG............ i will check it out.

DAvid

This is what I used to speed things up.
http://vbaexpress.com/kb/getarticle.php?kb_id=1035

Thanks SHG. I am still reviewing the code you sent. My skill level not quite the same as yours but i am good enough to review it and learn some things. So thanks.

Hi SHG,

I think i found an error in the code.

Recall the following section of code:

Please Login or Register  to view this content.

I think this should actually be the following (no equal sign)

Please Login or Register  to view this content.

I have not checked your latest code but i think it still has this issue. I will check your new code now. also, i modified it slightly so that the inputs to the procedure are the same as my old one.

Hi Shg,

In reviewing your code more closely i realized that the "sort" was very important. Without it the procedure would not work correctly. If i sort the ranges as is then data would not align correctly as beside the names are grades and if sort just the names then the grades would not line-up with the right names.

I really like your code though. Its very compact and i learned something from it.

the procedure i currently use has the following inputs:

Please Login or Register  to view this content.

so this is what i am working with. i cant use your code unless i sort all rows in the given range. what i wonder is if i should read each range into an array and compare just the arrays? suggestions.

hi Shg, below is the code i came up with based on your helpful inputs. I learned alot.

Please Login or Register  to view this content.

Hi Shg,

Sure i can do that. How do i calculate the time for a procedure to run? Do you know an easy way?

David

Hi Shg,

I ran two tests. One using your program and one using mine. I used the same names that were in your original spread sheet. I think the first list had ~1688 entries and the 2nd has ~ 1679 entries.

Yours took ~ 0.281 sec
Mine took ~ 39sec

Thats a BIGGGGGGGGGGGGG difference.

Shg,

Yours is definitely faster. Thanks.

Yes i will mark as solved.