Friends,

I think that for many of you this will sound as an "open door", but the use of arrays can really improve the speed of the execution of your VBA code.

the reason i'm making this topic, is because of the fact that i think this can't be said enough. For the last couple of months i've been building an excel add-in for custumers, through wich they can calculate the costs of activites they perform. My add-in make a lot of use of tables the user can import.

The heart of my add-in consitst of a lot of looping through these tables (worksheets). When i started building, i had heard of arrays, but never took the time to get myself started with it. As my customers demand calculation speed, the last couple of weeks i couldn't deny the fact that the use of arrays was something i had to get into. With succes!

For one of my big customers (in terms of the size of their data (rows and columns)) the calculation time for one of the biggest resources to activities, took about 6 to 7 seconds, before the use of arrays. After i implemented the use of arrays (and no longer did loops directly through the worksheet(s)), the calculation time dropped dramatically and endend at a total time of 0.8 seconds. I couldnt help to remove a small teardrop from my eye.. :-).

I really learned that the continues switching between VBA and worksheets costs a lot of calculation speed. It sometimes made me desperate, since my customers where irritated by the long performs of the code.

So, if you're building code that has as a main goal to loop through a lot of worksheets of data, i can really advise all of you to replace the worksheet(s) with an array, don't hesitate to ask questions :-).

Regards,

Bart