Memory Leak in Excel

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
Who is Participating?
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.
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

RayneAuthor Commented:
Thank you The_Barman :)
RayneAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.