Excel 2003 macro working very slow in Excel 2007 (xlsm format)

**kapil** · 12-14-2010, 03:55 AM

Hello Everyone,

I have a macro in Excel 2003 and I saved that file in Excel 2007 (xlsm) version; however, the code is working very very slow. In excel 2003 it used to take me 1-2 minutes but now its taking over 20 mins.

Below is the code that I have in excel 2003. it copies the data from another tab of the same spreadsheet. Please suggest if I need to make any changes in the code to make its speed better.

Thanks!
Kapil

Please Login or Register  to view this content.

**royUK** · 12-14-2010, 04:45 AM

I can't see why the code should be slower in 2007

**kapil** · 12-14-2010, 04:57 AM

Even I could not find the reason. It looks like to me that macro in 2007 is not overwriting the previously copied content and is creating a seperate space for the newly copied data on RAM / Cache memory.

I have come to this conclusion since the macro starts very fast; however, when it comes to row 100 it becomes little slow and at rows 800-900 it works painfully slow.

Have you ever heard of this situation or come across this situtation in office 2007 where Macro does not overwrite the previous content? if yes, what is the solution for this. Thanks.

**royUK** · 12-14-2010, 04:59 AM

It would probably be much more efficient if the data layout permits to use AutoFilter in the code instead of Looping. This would apply to any Excel version

**watersev** · 12-14-2010, 05:32 AM

I would suggest using array, it should work faster, 1-2 minutes for the code is too much.

I've created test file. Run macro "test"

**kapil** · 12-15-2010, 04:19 AM

Hi,

I am unable to resolve the slowness issue with the above mentioned suggestions.

I just checked that my system RAM is 512 MB. Is this a reason for slowness? I heard that Office 2007 applications requires more memory.

What should be my ideal RAM configuration to run these types of macros efficiently? Thanks.

**royUK** · 12-15-2010, 04:30 AM

That's the reason i would say, most PCs now have at 2gb. Memory is cheap now so check what your PC will accommodate & add as much as you can afford

**kapil** · 12-17-2010, 06:22 AM

Thanks Roy,

Increasing the memory of my system wil take some time. I will check the code again once my system RAM is increased.

I still have one question. In my above code I have found that the below step is taking very long then expected. System stucks for 10-20 secods while executing this step. Can I made some changes to it to increase its speed? Thanks again for all your valuable suggestions.

Please Login or Register  to view this content.

**kapil** · 12-17-2010, 11:26 AM

Hi,

Sorry for bothering you all again.

Note that I have checked that Macro on a different system as well which has RAM of 2GB. however there is no improvement in the speed.

Initially the Macro starts very fast (while copying upto 200 rows)... however, once the row count reaches 500+ the speed slows down... and during 1,000+ rows the speed slows down dramatically...

My spreadsheet has over 2,000 rows. Dont know how to improve the speed. It used to work fine in Office 2003.