+ Reply to Thread
Results 1 to 8 of 8

Macro Efficiency?

  1. #1
    Registered User
    Join Date
    03-02-2008
    Posts
    39

    Macro Efficiency?

    I have a macro that executes many calculations and then attempts to write out the data which is stored in column arrays to a worksheet. The calculations happen fairly quickly but when I try to write out the arrays to a worksheet it takes HOURS!

    I have attached an example workbook to show what I am doing but unfortunetley this example runs fairly quickly, unlike my full macro with all the calculations. The array size in my full macro is 86000, where it's only 2000 in this example. I had to reduce the array size in this example in order to reduce the file size so that I could upload. Even with array size of 86000 in this example workbook the macro runs quickly.

    I know its not the calculations in my full macro that causes the long run time because when I comment out the For Loops used to write out the data the macro runs quickly.

    I'm hoping to get some ideas of what could be going on because hours of execution time is unacceptable. Any help would be much appreciated.

    Thanks in advance.
    Attached Files Attached Files
    Last edited by wz4np1; 09-14-2010 at 10:14 AM.

  2. #2
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: Macro Efficiency?

    Try using

    Please Login or Register  to view this content.
    At the start of your code and then put

    Please Login or Register  to view this content.

    before you exit the sub. That should speed things up a fair bit.

  3. #3
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Macro Efficiency?

    Can you also post an xls-file ?



  4. #4
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Macro Efficiency?

    snb, there's always the Microsoft Compatibility Pack, no? Just a simple download away. Don't ask OPs to upload a file in an Excel version that is no longer supported by Microsoft. Get your own system up to speed instead.

    The object model has undergone considerable changes after XL 2003, so, saving an xlsm as an xls might do more harm than good. Worst case, you won't be able to do anything with it, because the 2007 code includes commands or objects/methods/properties that XL2003 does not support. There is no backwards compatibility for code that has been developed with 2007 or later. You may find that there are commands that don't compile in your version of Excel. So, posting an xls is not really an option.

    If you don't want to upgrade to 2007 or later, you may just need to skip questions that deal with those versions.
    Last edited by teylyn; 09-14-2010 at 10:16 AM.

  5. #5
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606

    Re: Macro Efficiency?

    snb - there is an Excel converter to convert 07 to 03.

  6. #6
    Registered User
    Join Date
    03-02-2008
    Posts
    39

    Re: Macro Efficiency?

    Thank you Andrew-R!

    That worked like a charm! Runs in roughly 40s now! HUGE improvement.

  7. #7
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Macro Efficiency?

    @teylyn, @SthephenR

    My question was aimed at wz4np1.

  8. #8
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Macro Efficiency?

    snb,
    my comment was for you and for the benefit of others. I wanted to help you understand the issues that may arise when converting an xlsm file to xls.

+ 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