The problem
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
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 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
|
Figure 3
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
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
|
Figure 5
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
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.
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
|
Figure 8
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
