Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: clearing up memory

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Posts
    197
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Does anyone have an hints on how to clean up memory while running a program. My program works great when I am dealing with under 5000 rows but when I start dealing with more and try to autofill I get an out of memory error. I have tried splitting up the autofill but that does not work either. Any suggestions would be great.

    Thanks-

  2. #2
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Hawley,

    I'm surprised that you are running into memory problems with only on the order of 5000 rows. Of course, the amount of memory requires depends on a lot of factors, like how complex the formulas are, how many columns are involved, and even whether cell formatting, validation, conditional formatting, etc., are involved.

    One way to considerably reduce the memory associated with complex formulas is to convert them to VBA UDFs. Usually, even a very complex formula can be converted to a UDF that has just a few arguments and makes the cell formula much simpler.

    The formula memory can be reduced even further by eliminating the cell formulas via the use of a VBA macro that does the calculations and just writes the results into the cells. This way, the cells contain only values, but no formulas. Macros can also replace conditional formatting, validation, etc., if these are contributing to the memory problem.

    If you would like an example of converting cell formulas to UDFs and macros, let me know and I will put together a simple one.
    Keep Excelling.

    Damon

    VBAexpert Excel Consulting
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm

  3. #3
    Board Regular wilkisa's Avatar
    Join Date
    Apr 2002
    Location
    Decatur IL, USA
    Posts
    635
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hawley, is this the same sheet you were working on last week? You were trying to autofill a formula over several thousand cells.

    I suggested how to clean up the hard drive to free up resources prior to your autofilling. Have you taken those steps?
    Thanks,
    Shirlene

  4. #4
    Board Regular
    Join Date
    Apr 2002
    Posts
    197
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    Yes, this is the sheet I was working on last week. I did clean up my hard drive and it did not help. Thanks for that suggestion though.

    Actually the sheet I am working with has 8744 rows and 114 columns. The formulas are complex and vary from cell to cell. I can not set the cell to just the value. I need the formula in there in case something in another cell changes. I'm not sure what a UDF is so if you could send me an example that would be great. Also if you want some examples of the formulas let me know.

    Thanks-

  5. #5
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi again Hawley,

    A UDF is a "user-defined function", or simply a custom function written in VBA.

    I will return some simple examples if you send me an email at

    VBAexpert@piadamon.com

    Also, if you want to send me your workbook or just some examples of the types of formulae you are using, that would help me to provide examples that are apropos.

    I am interested in doing this partly because I am curious myself as to just how much memory will be freed up using these methods.

    Damon
    Keep Excelling.

    Damon

    VBAexpert Excel Consulting
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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