Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Memory Leak in Excel

Posted on 2012-08-24
Medium Priority
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
  • 2
LVL 24

Assisted Solution

Steve earned 1000 total points
ID: 38328704
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

ID: 38328849
Thank you The_Barman :)
LVL 77

Accepted Solution

GrahamSkan earned 1000 total points
ID: 38328909
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

ID: 38330291
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

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

With its various features, Office 365 can not only help you with your day-to-day business tasks, it can also do wonders for your marketing campaign.
In this article, I will demonstrate that how to do a PST migration from Exchange Server to Office 365. This method allows importing one single PST, or multiple PST's at once.
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…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

579 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