Bizarre VBA issue - phantom VBA project files after workbook is closed

[iliace]

Earlier today, I encountered a very strange glitch that I have not seen before. I won't be able to list all the details of the problem, so I'm hoping if someone has encounted this before could point me in the right direction.

Here is the setup. I have a template that generates a file for each department. There is an external program that populates data into the template file, and then saves it as a new file for each department. However, because the external program does an equivalent of save-as, it uses the same file format as the template file. The template file is an XLSM, and contains the VBA code. The final bit of my code in the template does its own save-as, but saves the file in a XLSX format to get rid of all VBA code, then deletes the original XLSM. It looks something like this:

Dim p As String
Dim s As String
Dim n As String

p = ThisWorkbook.Path
s = ThisWorkbook.Name
n = Left$(s, InStrRev(s, ".")) & "xlsx"

ThisWorkbook.SaveAs FileName:=p & n, FileFormat:=xlOpenXMLWorkbook

Kill p & s

ThisWorkbook.Close SaveChanges:=False

(I'm leaving out a few details here for simplicity, such as checking for Application.PathSeparator and error handling)

This has been working fine, until I added another part called EventLog. It uses an Excel workbook on a network share to keep track of everything that happens, in a separate module that is periodically called to log the most recent action or error.

So here is the problem

The logging part is working correctly. The code above seems to work as intended - it saves the new file as an XLSX, deletes the XLSM, and closes the workbook. However, within the VBE, in the Project Explorer window, I still see all the different files, the new XLSXs, as if they are still open. There is a VBAProject for each one. Once there are too many, I get an error something like "Excel does not have enough resources to perform the current operation".

I tried the code in debug mode, and here's an interesting twist: none of these "phantom" VBAProjects are accessible. For example, after I have 100 of them open, I use this command in the Immediate window:

Debug.Print Application.Workbooks.Count

and the result is 3, instead of 103 as you might expect. If I try to address any of the by name, like so:

Application.Workbooks("PhantomWorkbook42.xlsx").Close SaveChanges:=False

I get an automation error saying the object is not available.

Nonetheless, these phantom VBA projects take up Excel/system resources and eventually cause the process to bum out.

Has anybody encountered a problem of this sort before? If so, please share.

Thanks,

-Ilia

 

[RickXL]

I have not had the problem but, if it were me, I think I would separate the macros and the template into different workbooks so that there would be no xlsm files to delete and no possible VBA Projects created.

 

[iliace]

I have not had the problem but, if it were me, I think I would separate the macros and the template into different workbooks so that there would be no xlsm files to delete and no possible VBA Projects created.

Thanks Rick. That is a good idea, and I will definitely be exploring this option. However, it's easier said than done. The external program that uses the template to populate data in each instance is set up to call a macro from within the instance, and that macro originates from the template. In other words, it's not the template that calls up the external program, it's the external program that calls up the template. So, at the very least, I would need to have minimal code in the template, which calls my preparatory code. Thus, it would still have to be an XLSM, just not containing the sensitive code that I don't want exposed to the end user.

One issue here is that I'm not the only one who may need to perform this process, but I am the only one who fully understands how it works, and with the technical background to execute and maintain the process. Even with documentation, adding a complication such as having an external add-in loaded that is called from within the template introduces certain execution risk in the event I'm unavailable. I would have to do a whole lot more development and some additional training before I could confidently hand it off to someone else in my department, as this is a mission-critical process for us.

 

[shg]

Have you looked at PHANTOM PROJECTS DISPLAYED IN VBE IN OFFICE 2010 - Microsoft Community?

 

[iliace]

Have you looked at PHANTOM PROJECTS DISPLAYED IN VBE IN OFFICE 2010 - Microsoft Community?

No, I have not. Thank you for the reference. I recently installed PowerPivot - sounds like that could be the source of the problem?

EDIT: Also, I believe Rob Bovey was actually wrong on this issue, it does cause problems from what I can tell. Of the ~250 templates I'm running, only about 150-180 (depending on system utilization) work before the phantom workbooks cause the problem. The files in question are of considerable size (3.5mb in my case) and having that many in "phantom" memory outpaces system resources.

Thank you so much for your input, shg. This just may solve my problem. I'll try it tomorrow.

 

[shg]

I've not experienced the problem, and so have nothing to add, but hope you get it sorted. Good luck.

 

[raulsanchez]

Just to add to the conversation, uninstalling my PowerPivot Add-In has solved this issue for me.

-Raul