Excel 2007 SP2 Macro Performance

Hi,

I have created an Excel workbook with macros to handle budgeting process. This is a very complicated workbook with several excel sheets. The macros in the excel workbook retrieves data from SQL database and stores those values in an temporary collection of a class (object) and once all the data has been retrieved from SQL, the macro then loops through the collection of class (object) and inserts value into excel worksheet.

In the macros I have tried to use best practices such as

• Turning off screen updates and disabling the events
• Turning the calculation mode to manual

Initially I created this workbook in Excel 2003 and when I use to run the macros they use to run at a very good speed.

Recently, I migrated to Excel 2007 and ever since workbook has been running really slow.

Interestingly, I have a test laptop with Excel 2007 (without SP2) and my workbook runs at normal speed but as soon as I install SP2 the speed of macro slows down considerably.

Is there something in Excel 2007 SP2 that could potentially slow down the macros?

Any help would be greatful.

Thanks.

After searching the web, there are many discussions about performance and SP2. I did notice a "named ranges" concern when going to SP2. Do you have many named ranges?

You might also read http://support.microsoft.com/kb/968863

In some of the report in the excel we do use quite a bit named ranges, but having said that in another part of application we do not use named ranges at all.

Looking at the link you provided does explain a bit of slowness hence I am going to apply this update and see if that makes any difference.

Will let you know shortly.

Thanks for that MarvinP.

That update seems to have solved my problem. The macros are now running at normal speed.

Thanks MarvinP for providing the link, was great help.