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

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 902
  • Last Modified:

Excel VBA Projects are not being released

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?

  • 4
1 Solution
Rory ArchibaldCommented:
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)
SVINCAuthor Commented:
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
SVINCAuthor Commented:
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
SVINCAuthor Commented:
I am closing this as I did not get any solution that solved the problem
SVINCAuthor Commented:
Closing the point

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now