+ Reply to Thread
Results 1 to 3 of 3

excel 2007 VBA eating memory

  1. #1
    Registered User
    Join Date
    08-07-2010
    Location
    Chile
    MS-Off Ver
    Excel 2007
    Posts
    2

    excel 2007 VBA eating memory

    Hi folks.

    Here's a nice little one. I have a little algorithm on a excel 2007 workbook (it's pure data, no formulae on any cell), in order to make it work i've coded a small macro which runs "n" iterations with the data contained on the spreeadsheets. Each iterations takes about 15 seconds.

    So far so good... pretty simple as you can see. The catch it's the 2.7GB of virtual memory excel 2007 is eating up on the 800th iteration until it hangs.

    The first 4 days i've optimized the macro code to a bare minimum of 40 lines having the same problem with the memory at the end.

    Now.... take a look at this.... i've run the macro on several machines with excel 2007 with the same problem... BUT on excel 2003 machines it doesn't use more than 50MB of virtual memory after 5000 iterations.

    Does anybody have a tiny little idea of what the h... can i do in order to profit of excel 2007 best performance and run at least 500.000 iterations?

    *************************************************
    <here's the code>

    Please Login or Register  to view this content.
    Last edited by tiroloco; 08-09-2010 at 09:03 AM.

  2. #2
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: excel 2007 VBA eating memory

    If there are no formulas, why do you keep turning calculation off and on? Also, you'd be better off writing ranges in one go rather than cell by cell:
    Please Login or Register  to view this content.
    rather than:
    Please Login or Register  to view this content.
    Remember what the dormouse said
    Feed your head

  3. #3
    Registered User
    Join Date
    08-07-2010
    Location
    Chile
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: excel 2007 VBA eating memory

    Thanks romperstomper

    I'm turning the calculation on and off cause the next step on this excel is adding some formulas which will take the values at the exact point where i'm turning calculation on and off.

    Thanks for the ranges tip.... it speeded up each iteration in about a 10%, however it keeps eating memory.

+ 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