none
VBA Out of Memory RRS feed

  • Question

  • Hi,

    I wrote my own add-in with lot of utilities I need for the daily job, it is not that rocket science, most can be done with the standard Excel features, I just wanted to have it with one mouse click. All at once I was getting the Out of Memory error and rebuild the workbook (export & import all modules and forms) and everything looked good but 2 "edits" later I had again the Out of Memory error.

    The (IMO) strange thing is that the add-in is working fine and all options do what is expected.

    Googling to solve this:

    • Limit the number of public variables (done)
    • Release large objects & variants (done)

    I found also some hits about the limits of a vba module (64kB). Some modules are above. Is this really a constraint?

    I am used to include a reference, say Scripting Runtime iso createobject(dictionary). What is the best memory management: a reference or createobject?

    What is best to use: public or property?

    Do you have other advises to get rid of Out of Memory? Thanks.


    Saturday, April 16, 2016 10:49 AM

Answers

  • Try testing in Excel with no other addins and in particular no ComAddins loaded. Rather than unloading them all it might be simpler to start with a 'clean' automated instance. Say in Word or with a vbs script start Excel like this

    Dim objXL As Object
    Set objXL = CreateObject("excel.application")
    objXL.Visible = True
    objXL.Workbooks.Add

    You could add another line to open your workbook/addin from file, or open it manually when Excel appears.

    If that makes a difference, your addin's project doesn't remain in the explorer when closed and/or now don't get the "out or memory" message", go through the process of elimination with other addins and ComAddins loaded.


    Wednesday, April 27, 2016 8:58 AM
    Moderator

All replies

  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel, I'll move your question to the MSDN forum for Excel

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.


    Regards,

    Emi Zhang
    TechNet Community Support


    Please mark the reply as an answer if you find it is helpful.

    If you have feedback for TechNet Support, contact tnmff@microsoft.com.

    Monday, April 18, 2016 10:24 AM
  • That Out of Memory error can arise for various reasons, which generally have little to do with memory! However it means without seeing your code we can only throw wild guesses. 

    Re reference or createobject: these go hand in hand, when you use createobject the created object is typically assigned to a reference until no longer needed, then should be released. When all references to the object are released the object will be destroyed.

    Re which better is public or property, these are not directly comparable. However if you can relatively easily avoid maintaining a public reference, ie retrieve the value or object as/when needed, that's probably better.

    Monday, April 18, 2016 2:26 PM
    Moderator
  • Hi,

    Thank you.

    Monday, April 18, 2016 4:38 PM
  • Hi Peter,

    Thank you for the feedback. Difficult to post the code because it is over 14000 code lines or course in  several modules (34) & 18 userforms.

    I found 2 modules with a size over 64 kB. Although the info found on the internet is rather vague about this 64 kB limit, I'll split the modules to make them less than 64 kB.

    The reason why I asked about public variables & properties is because MZTools has such option. I intend also to rework the references to CreateObject.

    Monday, April 18, 2016 4:51 PM
  • I doubt the issue is related to the so called 64kb limit. I've no idea what the actual limit is but I've had no problem with modules considerably larger than that. Whether that's good 'style' or not is another matter!

    Chances are your out of memory error is due to some particular code, possibly one line or some approach. Try and isolate when and where the problem occurs.

    Monday, April 18, 2016 7:56 PM
    Moderator
  • Hi, JP Ronse

    According to your description, I suggest that you could follow this helpful article to troubleshoot-memory-errors-in-excel:

    https://blogs.technet.microsoft.com/the_microsoft_excel_support_team_blog/2013/04/19/how-to-troubleshoot-memory-errors-in-excel-available-resources-out-of-memory-system-resources/
    Tuesday, April 19, 2016 1:27 AM
  • Re:  18 user forms

    Another guess...
    Are you absolutely sure that all the user forms get unloaded?
    I use an idea from Chip Pearson to clean them up...
    '---
    lngCounter = UserForms.Count
    If lngCounter > 0 Then
      For N = lngCounter To 1 Step -1
       Unload UserForms(N - 1)
      Next
    End If
    '---

    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)
    • Edited by James Cone Monday, October 31, 2016 11:46 PM
    Tuesday, April 19, 2016 5:17 AM
  • Hi Peter,

    Thanks for the tip, I'll review all code.

    Tuesday, April 19, 2016 4:26 PM
  • Hi David,

    Thanks. I'll check.

    Tuesday, April 19, 2016 4:26 PM
  • Hi Jim,

    I'll check but don't think it is the reason. Adding or modifying a few lines of code can be enough to raise the issue.

    Tuesday, April 19, 2016 4:29 PM
  • >>>I'll check but don't think it is the reason. Adding or modifying a few lines of code can be enough to raise the issue.

    According to your description, I suggest that you could help us figure out which line codes cause this issue, that will help us reproduce and resolve it.

    Thanks for your understanding.
    Wednesday, April 20, 2016 7:15 AM
  • Hi,

    I tried all memory trouble shooting suggestions without success. To be as complete as possible, I am using a developer workbook that is then saved as an add-in. Rebuilding the developer wb from scratch. After importing a module with some global declarations (about 5) & 2 userforms (disclaimer and about) and I do not see any code that I shouldn't use, at least the compiler has no problems with it.

    <sample code>

    Private Sub cmdOK_Click()
       CloseHelpfile
       Unload Me
    End Sub

    Private Sub Label2_Click()
       DisplayHTMLHelpTopic (JPE_HLP_THANKS_TO)
    End Sub

    Private Sub UserForm_Initialize()
        With Me
            .lblVersionNr.Caption = JPE_VERSION
            .lblBuildNr.Caption = JPE_BUILD
            If Year(Date) > CLng(YEAR_DEV) Then
                .lblCopyrightYears.Caption = YEAR_DEV & "-" & Year(Date)
            Else
                .lblCopyrightYears.Caption = YEAR_DEV
            End If
        End With
        With Me
          .StartUpPosition = 0
          .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
          .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
       End With
    End Sub

    Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
       If CloseMode = 0 Then
          Cancel = True
       End If
    End Sub

    <end sample code>

    I get the out of memory message when I close the workbook. I removed already all other add-ins without success. I close and restart Excel, open the workbook, save it as an add-in and load it, all is working as expected.

    What I do see is that, after closing the developer workbook, is that the project remains in the project window where I expect that it should be released.

    I googled on this and there are a lot of hits, tried several but none solved the issue.

    Sunday, April 24, 2016 10:56 AM
  • Do you have any recursive functions in your code without recursion level check?
    Do you have any huge memory allocations with Set (aka malloc)?
    Do you free used memory with Set nnn as Nothing where applicable?


    Best regards, George

    Sunday, April 24, 2016 11:22 AM
  • Hi George,

    Thanks for replying on a Sunday (for me afternoon). Answer on all questions is No. The code is even not running, it is just in the developer workbook that I have the issue. IMO because vba does not release the project I get the OOM message.

    Sunday, April 24, 2016 12:07 PM
  • Europe here too :)

    What if you test the code on another computer?


    Best regards, George

    Sunday, April 24, 2016 12:10 PM
  • >>>The code is even not running, it is just in the developer workbook that I have the issue. IMO because vba does not release the project I get the OOM message.

    According to your description, I suggest that you could try to repair Excel.

    For more information, click here to refer about Repair an Office application

    Monday, April 25, 2016 9:05 AM
  • Hi George,

    The code is running fine when installed as add-in. Using it daily and distributed to about 30 colleagues without complaints.

    F.i. it contains a module to convert string dates to real dates and I can run it over thousands cells without problem and it does it smoothly. So I don't think there is something wrong with the code on itself.

    Monday, April 25, 2016 3:02 PM
  • Hi,

    Thanks for the reply, afraid I don't have the rights to do this, the company has restricted the rights.

    Monday, April 25, 2016 3:03 PM
  • If the code is running ok, when do you get OOM?

    Best regards, George

    Monday, April 25, 2016 3:06 PM
  • George,

    That is the strange part, the add-in is working fine, I get the OOM after editing and saving/closing the dev workbook. Excel is even crashing (sometimes).

    As said, the project is not closed in the vba proj wdw.

    Monday, April 25, 2016 4:01 PM
  • Well, you wrote: "afraid I don't have the rights to do this, the company has restricted the rights" ... ... ...


    Best regards, George

    Monday, April 25, 2016 6:22 PM
  • Hi,

    I detected something strange.

    1. Open a new workbook. Visible in VB Proj wdw
    2. Close the workbook, Remains visible in VB Proj
    3. Open an new workbook and close it. Book1 has disappeared from the VB Proj Wdw.
    Tuesday, April 26, 2016 2:04 PM
  • Try testing in Excel with no other addins and in particular no ComAddins loaded. Rather than unloading them all it might be simpler to start with a 'clean' automated instance. Say in Word or with a vbs script start Excel like this

    Dim objXL As Object
    Set objXL = CreateObject("excel.application")
    objXL.Visible = True
    objXL.Workbooks.Add

    You could add another line to open your workbook/addin from file, or open it manually when Excel appears.

    If that makes a difference, your addin's project doesn't remain in the explorer when closed and/or now don't get the "out or memory" message", go through the process of elimination with other addins and ComAddins loaded.


    Wednesday, April 27, 2016 8:58 AM
    Moderator
  • Hi Peter,

    That makes indeed a difference, when I open my project and close it, it is indeed closed without the out of memory error and it is removed from the project explorer. I suspected already another add-in causing this behavior but maybe I did it not properly enough, meaning closing and restarting Excel, I will give it another try....

    Another thing I noticed, when I close the VB editor before closing my project, I don't get the OOM message anymore. Although the project is still visible when I reopen the VB editor. Adding and closing a workbook, doing nothing with removed the reference to my project.

    By disabling all add-ins (no com add-ins loaded) and reloading one by one, testing it, I found the one which caused the issue. Believe me or not, it was the latest I would suspect therefore I didn't unload it the first time, so sure that it couldn't be that one. I'll contact the publisher.

    As the publisher is a very well known Excel expert, I will not mention his/her name/add-in here but if you really want to know: jean-pierreDOTdegrooteATskynetDOTbe.

    Thanks for persisting in this thread.

    Wednesday, April 27, 2016 12:10 PM
  • It was your mention of the closed workbook's project remaining as a 'phantom' that jogged my memory the OoM message might not be related to your project but an addin or ComAddin, or perhaps something they have in common behind the scenes.

    It's a relatively rare scenario but it's puzzled some of us for a very long time, AFAIK nobody has ever pinned down the exact cause.

    It was sensitive of you not to blame the suspect addin's author, though in this case I'm confident it's nothing he might have easily avoided. However I'd urge you to refer him to this thread and (if possible) share your addin and see if he can replicate. If so it will be easier for him to identify the underlying factor in his addin, and perhaps indicate a more general issue. I'd also be interested!

    Wednesday, April 27, 2016 12:47 PM
    Moderator
  • It was your mention of the closed workbook's project remaining as a 'phantom' that jogged my memory the OoM message might not be related to your project but an addin or ComAddin, or perhaps something they have in common behind the scenes.

    It's a relatively rare scenario but it's puzzled some of us for a very long time, AFAIK nobody has ever pinned down the exact cause.

    It was sensitive of you not to blame the suspect addin's author, though in this case I'm confident it's nothing he might have easily avoided. However I'd urge you to refer him to this thread and (if possible) share your addin and see if he can replicate. If so it will be easier for him to identify the underlying factor in his addin, and perhaps indicate a more general issue. I'd also be interested!

    Hi Peter,

    I already did, my add-in is even not needed to reproduce the issue and I explained it in a detailed mail to the author. I found your email in your profile, will send you the details.

    Wednesday, April 27, 2016 1:16 PM