We help IT Professionals succeed at work.

Causes of Slow Processing Excel VBA Programs

I am executing Excel 2007 VBA programs on a DELL OPTIPLEX 760 with 3.2 gig and a Dual Core processor.  The programs are processor intensive. Actual functions that are called frequently in the execution of the programs are cuts and pastes, sorts, finds, and VLOOKUP.  The worksheets are loaded data from several large files, and the size of the workbook is around 92Mb. To avoid overhead, the display function is turned off as much as possible.

After some time of execution, the performance becomes very sluggish.  I try saving the workbook, and closing Excel, and I then restart the program where it left off, but the performance is still poor.

Restarting the computer does help, but I would like to understand what is causing the poor performance so that I can aleviate the issues such that I can keep the programs working at optimal speed.
Comment
Watch Question

Maybe you're 'leaking memory'?
Make sure you close all objects and set to nothing.  This includes other workbooks, odbc connections, etc.
You can see your memory 'expand' by running your program with the Task Manager on (processes = Excel).  The memory value will keep growing.

Scott C
VBA Developer
Commented:
Have a look at these links, they can give you some insight.  Also, why is the file so large (92MB), is that all data?  A lot of times, files can explode in size if you have a lot of different formatting added into it.  Try removing some formatting, and then run your processes and see if that helps.  Also, are there a lot of formulas, try turning to manual calculations when executing your code, so it doesn't have to recalc everything all the time.

Improving performance in Excel 2007:
http://msdn.microsoft.com/en-us/library/aa730921.aspx

Slow Response, Memory Problems, and Speeding up Excel:
http://www.mvps.org/dmcritchie/excel/slowresp.htm

Excel Calculation Bottlenecks:
http://www.decisionmodels.com/optspeedd.htm
Top Expert 2010
Commented:
Hello Kenz,

In addition to the above:

1) Turning off screen updates can save massive amounts of time
2) Change calculation mode from automatic to manual if possible (and then back to automatic when the code
wraps up)
3) Avoid unnecessary loops, and use array transfers whenever possible

Regards,

Patrick

Author

Commented:
None