Memory Leak in Excel

Posted on 2012-08-24
Last Modified: 2012-08-25
Hello All,

I am curious as to how developing the excel in VBA, can lead to any possible memory leaks ?
Like creating objects and Not setting them to nothing, etc.

All feedbacks and suggestion are welcomed. If you would let me of any possible scenario where this might happen and tips to avoid it, it will be awesome.

Thank you
Question by:Rayne
    LVL 24

    Assisted Solution

    One of the ways which I recently encountered:
    If you try to perfrom an ADO SQL Query on an open workbook.
    This is known to cause problems of memory leak.
    It is best to save a copy of the workbook and Query that copy rather than open workbooks.

    Another would be using Java Script Objects to modify PDF files from excel.
    This causes memory leak for which I have had to create a macro to terminate Acrobat once a certain memory level is reached (just before the system crashes)
    Code for that below:
    Dim ObjW As Object
        Set ObjW = GetObject("winmgmts://.")
        Dim ObjP As Object
        Dim ObjP2 As Object
        Set ObjP = ObjW.execquery("Select * from win32_process where Name = 'Acrobat.exe'")
        For Each ObjP2 In ObjP
        If ObjP2.workingsetsize > 108808256 Then ObjP2.Terminate
        Set ObjP = Nothing
        Set ObjW = Nothing

    Open in new window


    Author Comment

    Thank you The_Barman :)
    LVL 76

    Accepted Solution

    Setting object variables to Nothing doesn't do anything that isn't done automatically when the variable goes out of scope.

    I suspect that it is an attempt to fix a COM design problem regarding releasing the memory if the object is shared. That does cause a memory leak, unavoidable by the VB programmer, but generally unnoticed, but it's one reason that MS are slowly moving from COM to .NET.

    Author Comment

    Thank you Guys, for your replies so far, I will keep this question opened till late tonight to see if other experts have any further feedbacks

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Suggested Solutions

    Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
    This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now