Excel Does Not Quit

The symptom of the problem

The cause of the problem

Solution 1: Base every reference on an explicitly declared object

Solution 2: Use late binding

References

The symptom of the problem

A program, such as MS Excel, run via automation, does not quit even after being told to go away and all references to it are set to 'nothing.'  As shown in Figure 1 and Figure 2, the Windows Task Manager shows that Excel continues as a process even after the code controlling the automation completes.  In addition to using up system resources, it can lead to subsequent problems.

In Figure 1, just prior to the execution of the xlApp.Quit statement, the Windows Task Manager window shows that the EXCEL.EXE process is running.

Figure 1

However, as Figure 2 shows, even after execution of the Set xlApp=Nothing statement, the EXCEL.EXE process is still active.  And the same will remain true after the controlling code has completely finished.

Figure 2

The cause of the problem

The most common cause of the problem is a 'global' reference to the automated application.  Unfortunately, under some circumstances it is possible to directly refer to an entity (property/method/object) of the automated object. This reference effectively is global to the calling application.  Hence, the reference remains in place as long as the calling program is active.  Consequently, the operating system will not end the automated application while the caller is active.  In Figure 3, the line in red is a reference to the Excel Range property.  However, it is not qualified with either xlApp or xlWB.  Consequently, it is global to the calling program.  This global reference to an Excel object forces Excel to stay active until the caller itself terminates execution.

Option Explicit

Sub testIt()
    Dim xlApp As Excel.Application, _
        xlWB As Excel.Workbook, _
        IStartedXL As Boolean
    On Error Resume Next
    Set xlApp = GetObject(, "excel.application")
    On Error GoTo 0
    If xlApp Is Nothing Then
        Set xlApp = CreateObject("excel.application")
        IStartedXL = True
        End If
    Set xlWB = xlApp.Workbooks.Add
    Range("a1").Value = Range("a1").Value + 1
    MsgBox xlApp.ActiveSheet.Range("a1").Value
    xlWB.Close False
    If IStartedXL Then xlApp.Quit
    Set xlWB = Nothing
    Set xlApp = Nothing
    End Sub

Of course, to use the above code one must set a reference to the Excel Object library as shown in Figure 4.  And, that, in itself is a clue on one possible way to avoid the problem!  We will see a solution based on not setting a reference to the library a little later.

Figure 4

Solution 1: Base every reference on an explicitly declared object

Ensure that every property / method / object within the automated application is referenced through -- and only through -- an explicit variable declared in the calling program.  That will ensure that there is no behind-the-scene direct reference from the calling program to the automated application.  To use this method with the code in Figure 3 the correct approach is shown in Figure 5.  The offending statement (in red in Figure 3) is replaced by the statement in green in Figure 5.

Option Explicit

Sub testIt()
    Dim xlApp As Excel.Application, _
        xlWB As Excel.Workbook, _
        IStartedXL As Boolean
    On Error Resume Next
    Set xlApp = GetObject(, "excel.application")
    On Error GoTo 0
    If xlApp Is Nothing Then
        Set xlApp = CreateObject("excel.application")
        IStartedXL = True
        End If
    Set xlWB = xlApp.Workbooks.Add
    xlApp.Range("a1").Value = _
        xlApp.Range("a1").Value + 1
    MsgBox xlApp.ActiveSheet.Range("a1").Value
    xlWB.Close False
    If IStartedXL Then xlApp.Quit
    Set xlWB = Nothing
    Set xlApp = Nothing
    End Sub

Now that there is no implicit connection established between the caller and the called program, the automated application (MS Excel in this example) correctly goes away as shown in Figure 6.

Figure 6

Solution 2: Use late binding

Establishing a compile-time reference to the automated application as shown in Figure 4 is useful for many reasons.  However, one of the few downsides is that it allows the use of unqualified statements, which, in turn, lead to unintended connections between the calling program and the automated application.  One way to avoid such unqualified references, or at least test for them, is to remove the compile-time reference.  That will, of course, require that the use of the Excel.Application and Excel.Workbook objects be replaced by the generic Object data type.

The advantage is that the compiler will flag unqualified references as compile-time errors as shown in Figure 7.

Figure 7

The correct code using late binding is shown in Figure 8.

Option Explicit

Sub testIt()
    Dim xlApp As Object, _
        xlWB As Object, _
        IStartedXL As Boolean
    On Error Resume Next
    Set xlApp = GetObject(, "excel.application")
    On Error GoTo 0
    If xlApp Is Nothing Then
        Set xlApp = CreateObject("excel.application")
        IStartedXL = True
        End If
    Set xlWB = xlApp.Workbooks.Add
    xlApp.Range("a1").Value = _
        xlApp.Range("a1").Value + 1
    MsgBox xlApp.ActiveSheet.Range("a1").Value
    xlWB.Close False
    If IStartedXL Then xlApp.Quit
    Set xlWB = Nothing
    Set xlApp = Nothing
    End Sub

References

Brought to my attention by Greg Truby is this comprehensive article from Microsoft that address other types of problems that arise from unqualified global references

INFO: Error or Unexpected Behavior with Office Automation When You Use Early Binding in Visual Basic
http://support.microsoft.com/default.aspx?scid=kb;en-us;319832

By accessing any page or link on this web site other than this page, you agree to the terms and conditions.

Copyright © 2000-2009 Tushar Mehta.
Send comments and suggestions about the web site to web-underscore-contact@tushar-hyphen-mehta-dot-cee-oh-em
Last edited December 27, 2012