Excel macro in 2007 runs extremely slow

[Acie]

I have this macro that typically ran from 12-15 minutes in Excel 2003. There
is alot of computations, and moving data(small data) around. On a similar PC
it takes about 60-70 minutes when it runs on Excel 2007 with SP1.

Other smaller macros take about the same time in both Excel 2003 & Excel
2007. This has me perplexed.

Any ideas ?

 

[Jon Peltier]

Tell Microsoft about it:

http://blogs.msdn.com/excel/archive/2009/01/06/vba-excel-performance-your-feedback-requested.aspx

- Jon

 

[JLatham]

As someone else noted recently (with sly grin on their face), the apparent
difference in performance of 2007 vs 2003 is a 'feature' of 2007.

You're probably going to need to look hard at your code and what it is
doing, and how it is doing it to get performance improvement.

However, one thing that can improve performance dramatically is the simple
inclusion of the
Application.ScreenUpdating=FALSE
statement near the beginning of your macro code. That keeps the sheets
displayed from having to be refreshed with each new calculation/data move, or
when you jump around in the workbook via sheetName.Activate in your code.

You might take a look at www.DecisionModels.com for other helpful hints.

 

[Roger]

I tried adding the statement Application.ScreenUpdating=FALSE
to my two Macros.
There was an improvement. Adding a single cell reference formula now only
takes 1:45 instead of 3 minutes.
See my other posts under "Excell 2007 - Slow &can;t run macros" of 3/1/2009