The author of this tip is Tushar Mehta. You can reach him through his web site:
Over time, it has become increasingly easier to use VBA code in one Office program to automate another similar program. For example, a single statement such as
Set xlApp =new Excel.Application
is all that is needed to run Excel. However, for all its ease, there are some unpleasant side-effects that could hurt the unwary developer. This tip addresses two such issues.
However, before getting to the issue of side-effects, there is the question of early binding vs. late binding. The above statement mandates the use of early binding, and in some cases that may not be so desirable. This tip addresses not only the unpleasant side-effects but also works with both early and late binding.
Once a program is started (instantiated with the above Set statement) it is the developer's responsibility to manage it. For applications (such as Excel) that can have multiple concurrent copies running, each execution of a statement like the one above starts up a new copy! Hence, it is the developer's responsibility to terminate each executing copy with a statement like
Otherwise memory will be littered with orphaned copies of the program that can only be terminated through the Windows Task Manager (activated with CTRL+ALT+DELETE).
On the other hand, for an application (such as PowerPoint) that can have only one copy running, a statement similar to the above Set statement will connect to the already running copy, if one is present. A subsequent .Quit would be most annoying to a user who started the program and is using it to do some work!
A solution addressing both problems
Unlike the Set statement used above, it is possible to distinguish between connecting to an already running copy and connecting to a newly started copy of the program with the use of the GetObject() and CreateObject() VB functions. Being able to distinguish between the two conditions means it is also possible to decide whether or not to terminate the program.
In the code below, which uses early binding, the GetObject() function, wrapped inside an error handler will connect up with an already running copy of the application (PowerPoint in this case). If the variable ppApp is still nothing after the GetObject() function, clearly there is no copy of PowerPoint already running. So, the CreateObject() function starts up the application and get a reference to this just-started copy. In this case, the code also sets the flag IStartedPP.
Once the developer is done with whatever work needs to be done, the IStartedPP flag is a reminder of whether this code started the other application or not. If it did, the other application is told to quit. Finally, and unconditionally, the ppApp variable is finally set to Nothing.
Dim ppApp As PowerPoint.Application, IStartedPP As Boolean
On Error Resume Next
Set ppApp = GetObject(, "powerpoint.application")
On Error GoTo 0
If ppApp Is Nothing Then
IStartedPP = True
Set ppApp = CreateObject("powerpoint.application")
'Do whatever stuff
If IStartedPP Then
Set ppApp = Nothing
The code in this tip addresses the question of properly cleaning up after oneself when connecting with other applications, some of which may already be running and others may be initiated by the code. In addition, the code works with both early-bound and late-bound clients.
An introduction to early binding and late binding:
Early and Late Binding
Automating one office application from another:
Creation of Object Variables to Automate Another Office Application
Why an application doesn't quit even when it is told to go away:
Program won't quit
Article ID: 555191 - Last Review: 14 Feb 2017 - Revision: 1