asked on

Make Excel VBA faster?

Hi,

I have a VBA macro in Excel that loops through many ranges. Is there a way to dramatically speed this up? Is there some way to turn updating off? Or can I open the workbook in some kind of non-visual mode using COM, will that speed things up?

Any other performance tips?

Brian Mulder

Hello jskramer,

you could try
http://www.microsoftexceltraining.com/VBA/SpeedingUpVBACode.htm
http://www.cpearson.com/excel/optimize.htm

HAGD:O)Bruintje

ASKER CERTIFIED SOLUTION

byundt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

leojl

hello,

It sounds like you are dealing with data
that should be in an array. You can take data
from spreadsheet cells and put that in an array.
then you can do all the looping things to the array
which is much, much faster than looping thru cells.

after you do the job with the array then put the
array results into the spreadsheet cells.

leo

cri

See the accepted answer here: https://www.experts-exchange.com/questions/20421681/Speeding-up-multiple-nested-Do-While-Loops.html

As already stated: Put the whole 'range en bloc' in an array (see example in my link) and manipulate it.

cri

And: Make sure you do not use Select, especially not inside loops.

jskramer

ASKER

Unfortunately I can't take advantage of arrays as my code is very strange but I can see how that would be very useful.

ScreenUpdating is what I was looking for. Turning off recalculation also sounds like a great idea.

Thanks for the other answers as well.

leojl

you are welcome.
we all learn from this.
leo