Excel VBA Projects are not being released

Posted on 2012-09-06
Last Modified: 2012-10-24
Hi, my company uses a large number of macros for specific job related tasks. This includes routine manipulation of data within different sheets in a workbook and also between ranges within a sheet. Sometimes ranges are copied between different workbooks. In addition ranges need to be paste-linked into a word document associated with the workbook. There are also macros which generate maps in MapPoint. Generally it can be said that there is one Excel workbook and one Word document for every job

As the macros need to be used by 5-10 persons simultaneously these are stored in an Excel addin on a network server. AddIn is not copied to the local system but accessed from the server

Each person would access multiple workbooks and documents simultaneously because information needs to be compared visually/copied across different jobs

The problem we are observing is that after a job workbook is closed the VBA project for that workbook remains open and is visible in the VB editor though the file is closed. Sometimes Excel crashes and from time to time the file gets corrupted also. Occasionally there is an Out of Memory error. Macros run slow

After doing a search on the internet the most likely culprit appears to be that objects created by VBA code are not being released and thereby Excel does not release the VBA project even after the file is closed. Multiple instances of Excel are visible in the task manager also apart from the VBA projects still being visible in the editor even after closing the file

There are anywhere between 5-10,000 lines of code and it is written in an Excel AddIn. A fair bit of checking has been done to confirm that objects are being released. Error handling has also been added to release all objects in case of errors

The Windows version is Windows 7 (64 bits). MS Office version is 2010 (both 32 bits & 64 bits). MapPoint version is 2013. All code has been written in VBA in an AddIn and development using the Excel VB editor

What is the best way to narrow down on the cause of this? What are the best tools to help identify/localize the problem?  Are there any other possible causes to explore

Do you need any more inputs?

Question by:SVINC
    LVL 85

    Expert Comment

    by:Rory Archibald
    Try disabling all COM addins and then running your code and see if the problem persists. Whenever I have seen this issue it has related to the presence of a COM addin (PowerPivot causes it all the time for me once I've used it in a session)

    Author Comment

    Thanks Rorya. I checked that out and after some experimentation I found that in this case it is not linked to other COM AddIns. The problem only occurs with files where the code I mentioned above is used

    I checked further on the net and found a mention about unqualified ActiveCell, ActiveSheet and Selection usage also causing such problems. I will go through the code and see if that helps

    Meanwhile, looking forward to any other suggestions

    Author Comment

    There is one more aspect I did not mention in my original post. All files that use this code have a reference to the AddIn added via the VB Editor because there are UDFs also defined in the AddIn which are used in various sheets as formulas. Wonder if that is a possible cause of this problem

    Accepted Solution

    I am closing this as I did not get any solution that solved the problem

    Author Closing Comment

    Closing the point

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    I'm writing to share my clumsy experience in using this elegant tool so you can avoid every stupid mistake I made. (I leave it to the authorities to decide if this deserves a place in the Knowledge archives.)  Now that I am on the other side of my l…
    I would like to show you some basics you can do with Mailings in MS Word. It´s quite handy feature you can use for creating envelopes, labels, personalized letters etc. First question could be what is this feature good for? Mailing can really he…
    This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
    This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

    737 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

    18 Experts available now in Live!

    Get 1:1 Help Now