Search code examples
excelvbaide

Closed excel workbooks remain in VBA IDE

Summary

My dead simple Excel workbook myTestBook.xlsb has a single empty table and a single code module with the routine test_openclose() inside. This routine just opens another Excel workbook (Mappe3.xlsx), then closes that workbook again.
When the routine is run (Alt-F8) with the VBA IDE closed, everything is fine.
When the routine is run (Alt-F8) with the VBA IDE opened, the intermittently opened workbooks keep getting listed in the IDE's project explorer. Each repetitive run leads to another entry in the IDE's project explorer.
Why is that and what can I do against this effect?

View after 6 runs with closed IDE (no entries) and 3 runs with IDE open (3 entries):

You can also see that the Workbook Mappe3.xlsx which is getting imported, is very simple too: just a single (empty) table, no named ranges, no internal or external references, no modules.

Code

I am using
° MS Windows 10 Pro x64, 10.0.19042
° Excel365 (V2201 - 16.0.14827.20158, 64bit)
° Microsoft Visual Basic for Applications 7.1, Retail 7.1.1119, Forms3: 16.0.14827.20024

Option Explicit

Sub test_openclose()
    Dim srcBook As Excel.Workbook
    Dim name As String
    
    name = "C:\Users\user1\Desktop\Mappe3.xlsx"
    
    ' Open a workbook:
    Set srcBook = Workbooks.Open(filename:=name)
    
    ' Do something useful,
    ' e.g. enter the current time:
    srcBook.Sheets(1).Range("B2").Value = Str(Now)
    
    ' Close the workbook and destroy the object
    Workbooks(srcBook.name).Close savechanges:=False
    Set srcBook = Nothing

End Sub

What I have tried

I have checked that no "exotic" references are ticked:
enter image description here

I have also checked that no "exotic" add-ins are active: enter image description here enter image description here

I have checked for similar questions and answers, but the suggested solutions do not apply to my case:

What else can I try?

Solution

  • The effect does not show when the workbook is closed differently:
    with the code

        ' Close the workbook
    srcBook.Close savechanges:=False

    instead of

        ' Close the workbook and destroy the object
    Workbooks(srcBook.name).Close savechanges:=False
    Set srcBook = Nothing

    the effect is not observed.

    Thankyou VBasic2008 for the above comment!