Application.Quit closes all open Excel files

Question

I want to close the active macro workbook inside the Userform_Terminate event. The problem I am facing is that, a ghost window of excel application lingers on even after workbook has been closed.

Have tried most of the suggested ways, I could get my hands on (described in detail in the code snippet) but to no avail. If anybody can help, much grateful.

NOTE: Have released almost all excel related objects from memory by setting it to nothing.

Code :

Private Sub UserForm_Terminate()

'   Application.DisplayAlerts = False    ' The excel ghost window lingers on
'   ThisWorkbook.Close , False
'   Application.DisplayAlerts = True
'
'   Application.DisplayAlerts = False    ' The excel ghost window lingers on
'   ThisWorkbook.Saved = True
'   ThisWorkbook.Close , False
'   Application.DisplayAlerts = True

'   Application.DisplayAlerts = False     'The excel ghost window lingers on.
'   ThisWorkbook.Close , False
'   Application.Quit
'   Application.DisplayAlerts = True

    Application.DisplayAlerts = False     'Ghost window is closed but also kills all instances of excel currently open
    Application.Quit
    Application.DisplayAlerts = True

   'NOTE:
   'Have released all excel related objects from memory by setting it to nothing, post use.

End Sub

Snap:

Answer 1

Well, your "gost" problem has the next explanation:

1. An Excel session/instance means the same Application handler. If you open a workbook from the Excel existing interface, it is open in the same instance. Pressing Ctrl + F6 will jump to the next workbook open in the same instance...

If there are open workbooks not seen in the Ctrl + F6 sequence, this only means that they are open in a different instance.

Another instance is open, for instance :), in this way:

Din ExApp as Object
Set ExApp = CreateObject("Excel.Application")
  ExApp.Workbooks.add 'without this line, the instance is quit by itself...
  Set ExApp = Nothing 'this only releases the memory

You can see more open Excel instances (if they exist) looking in TaskManager and seeing more the one such application (Excel.exe)...

2. When you close a workbook, and this specific workbook is the single one of the instance, the application Window, what you name a "gost" remains!. If there are some other workbooks open, the so named "gost" window disappears, too.

3. In order to handle both situations, please try the next approach:

Private Sub UserForm_Terminate()
        If Workbooks.Count > OpenWb Then 
            ThisWorkbook.Close , False
        Else
            Application.Quit
        End If
End Sub

Function OpenWb() As Long
   Dim count As Long, wb As Workbook, arr
   For Each wb In Workbooks
        arr = Split(wb.Name, ".")
        If UCase(arr(UBound(arr))) = "XLSB" Then count = count + 1
   Next
   OpenWb = count + 1
End Function

Quitting the application is done here only because you asked for it... When you try programmatically to open and close many workbooks, it is more efficient to keep the application open. To open a new instance takes time. To open a workbook in an existing instance takes less time... But to do that, your code must find that existing instance:

Sub testExcelInstance()
  Dim Ex As Object
  
  On Error Resume Next
  Set Ex = GetObject(, "Excel.Application")
  If Ex Is Nothing Then
        Err.Clear: On Error GoTo 0
        Set Ex = CreateObject("Excel.Application")
  End If
  On Error GoTo 0
  Ex.Workbooks.Add 'just doing something after having the Excel instance object
End Sub

Releasing the objects from the memory does not do anything, in terms of the object itself existence. If physically disappears only if you quit it.