+ Reply to Thread
Results 1 to 14 of 14

Out of Memory

  1. #1
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Out of Memory

    On occasion I get an Out of Memory error in 2007. It seems to come up the most when I'm saving a file or closing Excel, but not exclusively at that time. Every file that I'm saving is saved fine. It happens with different files, so I have a hard time believing that it's in a file.

    When I restart 2007 and reopen the files that were open everything is fine. I don't close anything or any other change to my system, only restart 2007. It does seem to happen more often as I increase the size of the file I'm working in, but when I restart 2007 and reopen the files, everything works fine.

    My 1st thought was that there is some sort of memory leak in 2007, that Excel doesn't release memory when it's done with it. This was a problem with my database programming. Since I'm constantly adding code to modules, that makes the most sense to me, but I don't see that listed as a possibility in Help.

    I have looked in Help and I'm tentatively reaching the conclusion that I'm hitting a 64 K boundary. Is there any way to determine how I'm hitting the boundary, and what I can do about it?

    My only other thought is that one of the modules in my personal file is too large, but my largest module is only 987 lines, and it doesn't happen every time, so it's hard to believe that my personal module is too large.
    Foxguy

    Remember to mark your questions [Solved] and rate the answer(s)
    Forum Rules are Here

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Out of Memory

    If you code that heavily, do you properly release all memory reserved by variables? For instance RANGE variables that have been filled with large ranges, be sure to set all of them back = Nothing when you're done with them.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Out of Memory

    That seems like a small possibility. I don't set large ranges into variables (that I can remember).

    But I thought that when the sub ended, all the variables were released at that time.
    Are you saying that I should specifically release variables before the sub ends?

  4. #4
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Out of Memory

    Quote Originally Posted by foxguy View Post
    But I thought that when the sub ended, all the variables were released at that time.
    Where did you get that idea?

    Are you saying that I should specifically release variables before the sub ends?
    Yes, I think I specifically said that.

    Variables like Long and Double...those are extremely small variable using virtually no memory, but object-based variables can reserve a lot of memory. I manually free them all as a matter of habit. I don't usually do that with workbook and worksheet variables, but I probably should do those as well.

  5. #5
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Out of Memory

    I'm curious.

    Do you happen to know of any site that discusses this problem?

    I just remembered that I do have this kind of issue. I have a sub that places ranges into variables that are passed to it ByRef. So those variables can't be released until the calling sub ends. I'm hoping to get a better understanding of this issue to determine how to handle it.

    I also just remembered that I use object variables to hold classes in them. I don't specifically release those variables because when the sub ends the variable goes out of scope and the terminate() of the class runs. Doesn't that indicate that the variable is being released?

  6. #6
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Out of Memory

    Forgot to answer your question.

    I got the idea that when a variable goes out of scope that it is released from memory. I don't know where I got that idea, I would guess that I assumed it's common knowledge. It's been in my head for so long, I don't know where I originally got it.

    Am I wrong, and can you point me to somewhere that I can read about it?

  7. #7
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Out of Memory

    When you google Excel VBA memory and Excel VBA memory leak you get many discussions.

  8. #8
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Out of Memory

    I may be wrong about RANGE and OBJECT variables declared within a normal sub that terminates properly.

    But I do remember clear warnings read somewhere about module-level and class-level variables (like Collections), they always should be cleared when no longer needed.

  9. #9
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Out of Memory

    I sure hope you're wrong about Range and Object variables. I use them a lot. If they aren't released when they go out of scope, I'm in trouble. That's a big job to go into all my subs and specifically release all Dimmed ranges and objects.

    Any one know of any place to find out whether Range and Object variables are released from memory when they go out of scope?

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

    Re: Out of Memory

    I'm pretty sure that your variables will be cleared when the Workbook is closed & closing Excel will make certain.

    Try running Rob Bovey's Code Cleaner on problem workbooks
    Hope that helps.

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

    Free DataBaseForm example

  11. #11
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Out of Memory

    Roy;

    That does solve my problem, but I'd like to figure out how to avoid the problem in the first place.

    I think I might have figured it out. I hardly ever close my code modules. I just read in a web site that keeping code modules open consumes a lot of memory. I even have a macro for closing all my modules, but I hardly ever use it. Hopefully that is my problem.

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

    Re: Out of Memory

    If you read the comments about Code Cleaner you will see
    During the process of creating VBA programs a lot of junk code builds up in your files. If you don't clean your files periodically you will begin to experience strange problems caused by this extra baggage. Cleaning a project involves exporting the contents of all its VBComponents to text files, deleting the components and then importing the components back from the text files.
    It's a consequence of using the VB Editor in files that you are constantly working on. Periodic cleaning is required

  13. #13
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Out of Memory

    I do clean my code periodically. I have never noticed any improvement after cleaning my code, so I don't think that is/was my problem.

    I think my problem was just too many code modules open, but only time will tell.

  14. #14
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Out of Memory

    If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

+ 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