Excel VBA: How to Unload Projects
In Microsoft Excel 2013 (and 2010, 2007, 2003, '97) when I closed an XLSM file, it was removed from the Excel VBA Project explorer.
However, in Microsoft Excel 2016, a closed XLSM file remains in the VBA Project explorer. In fact, even when there is only ONE XLSM file open in Excel, multiple instances of that XLSM file all having the SAME name can appear in the VBA project window!
This can cause serious problems . . . when I'm coding in one instance, when I *think* I'm coding in another.
I have no special Add-Ins. This problem was introduced solely by upgrading from Excel 2013 to 2016.
How can I remove projects from the VBA Project explorer that are associated with XLSM files that are closed? Or, better yet, how can I force Excel to automatically remove them from the Project explorer when I close them?
Thank you in advance.
However, in Microsoft Excel 2016, a closed XLSM file remains in the VBA Project explorer. In fact, even when there is only ONE XLSM file open in Excel, multiple instances of that XLSM file all having the SAME name can appear in the VBA project window!
This can cause serious problems . . . when I'm coding in one instance, when I *think* I'm coding in another.
I have no special Add-Ins. This problem was introduced solely by upgrading from Excel 2013 to 2016.
How can I remove projects from the VBA Project explorer that are associated with XLSM files that are closed? Or, better yet, how can I force Excel to automatically remove them from the Project explorer when I close them?
Thank you in advance.
Is this connected to a particular workbook or workbooks?
ASKER
No.
ASKER
All workbooks.
If it was a specific workbook/workbooks I was going to suggest a fix but since it isn't it sounds like you might need to Repair your Excel installation.
Try completely closing Excel and reopening it.
ASKER
IT has already repaired the Office 2016 installation. This behavior is linked directly to the upgrade from Office 2013 to Office 2016. It is very frustrating.
Try adding Set ThisWorkbook = Nothing when you close your workbooks.
ASKER
I close my workbooks by issuing either CNTL-F4 or ALT-F4 from withing the Microsoft Excel UI. So are you suggesting that I implement an "on workbook close" event and put "Set ThisWorkbook = Nothing" in there?
Yes.
ASKER
I put this code in the "ThisWorkbook" object:
Private Sub Workbook_BeforeClose(Cance
Set ThisWorkbook = Nothing
End Sub
It generates this compile error:
"Invalid use of property"
Private Sub Workbook_BeforeClose(Cance
Set ThisWorkbook = Nothing
End Sub
It generates this compile error:
"Invalid use of property"
ASKER
I tried this in a module and it did not generate an error, but also did not remove unload itself from the VBA Project explorer:
Private Sub Auto_Close()
Dim wb As Workbook
Set wb = ThisWorkbook
Set wb = Nothing
End Sub
Private Sub Auto_Close()
Dim wb As Workbook
Set wb = ThisWorkbook
Set wb = Nothing
End Sub
Do you have any Add-Ins installed in the workbook?
ASKER
I have already deleted all Add-Ins:
Excel Add-ins
COM Add-ins
Actions
XML Expansion Packs
The problem persists.
Excel Add-ins
COM Add-ins
Actions
XML Expansion Packs
The problem persists.
ASKER
All found under:
Excel->Options->Add-ins->M
Excel->Options->Add-ins->M
Sorry but I’m out of ideas except to suggest that you contact Microsoft support because there are a lot of threads on the web that talk about problems with Office 2016.
Yes, I experience the same symptoms - sometimes. I may be related to errors where the Macro code "blows up" or exits with uncaught errors or doesn't compile. Excel just gets "confused". I'll try to pay closer attention to the conditions.
An obvious "quirk". I can reproduce the problem with macro or even plain Excel files in 2010 and 2013. When having the "VBA Project" window open and opening and closing several files, duplicates of previous closed files are (often) still listed, as you indicate. Two work-arounds ...
Notice in the example below there are no "Properties" with the duplicate "ghost" file. An odd work-around is to click the "View Microsoft Excel" button on ANY file which HAS Properties. This then purges the ghost entries (at least in my testing).
Another work-around is to close the macro window entirely and reclick the "Developer | Visual Basic". It then comes up clean.
Hope this helps.
Notice in the example below there are no "Properties" with the duplicate "ghost" file. An odd work-around is to click the "View Microsoft Excel" button on ANY file which HAS Properties. This then purges the ghost entries (at least in my testing).
Another work-around is to close the macro window entirely and reclick the "Developer | Visual Basic". It then comes up clean.
Hope this helps.
Completely closing Excel and reopening it will fix the problem.
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.