+ Reply to Thread
Results 1 to 9 of 9

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

  1. #1
    Registered User
    Join Date
    07-09-2010
    Location
    Delhi NCR, India
    MS-Off Ver
    Excel 2003
    Posts
    46

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

    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.

  2. #2
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

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

    I can't see why the code should be slower in 2007
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  3. #3
    Registered User
    Join Date
    07-09-2010
    Location
    Delhi NCR, India
    MS-Off Ver
    Excel 2003
    Posts
    46

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

    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.

  4. #4
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

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

    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

  5. #5
    Forum Expert
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

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

    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"
    Attached Files Attached Files
    Last edited by watersev; 12-14-2010 at 05:46 AM.

  6. #6
    Registered User
    Join Date
    07-09-2010
    Location
    Delhi NCR, India
    MS-Off Ver
    Excel 2003
    Posts
    46

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

    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.

  7. #7
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

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

    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

  8. #8
    Registered User
    Join Date
    07-09-2010
    Location
    Delhi NCR, India
    MS-Off Ver
    Excel 2003
    Posts
    46

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

    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.

  9. #9
    Registered User
    Join Date
    07-09-2010
    Location
    Delhi NCR, India
    MS-Off Ver
    Excel 2003
    Posts
    46

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

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1