Shutting Down Objects Created by Using Automation

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

A local variable is normally destroyed when the procedure in which it is declared is finished executing. However, it is good programming practice to explicitly destroy an application-level object variable used to automate another application by setting it equal to the Nothing keyword. Doing this frees any remaining memory used by the variable. For some Application objects, you might also have to use the object's Quit method to completely destroy an object variable and free up the memory it is using. As a general rule, it's safest to do both: Use the Quit method and then set the object variable equal to the Nothing keyword.

There might be situations where you must determine if the instance of an application you are working with was created by your code before shutting it down. Generally, you can inspect the UserControl property of the Application object to determine if your code opened the current instance. However, there are cases where the value of the UserControl property can change from False to True as your code executes. For example, if you start Microsoft® Excel through automation, make it visible, and make it possible for the user to interact with this instance, such as by typing something in a cell, the UserControl property will return True even though your code started the instance. To handle this situation, assign the value of the UserControl property to a variable right after you create the instance of the Application object, and use this variable to test the value of the UserControl property before closing the application, as shown in the following example:

Sub GetObjectXL()
   Dim xlApp               As Excel.Application
   Dim blnUserControl      As Boolean
   
   Const ERR_APP_NOTRUNNING As Long = 429
   
   ' Set blnUserControl to True as default.
   blnUserControl = True
   On Error Resume Next
   ' Attempt to open current instance of Excel.
   Set xlApp = GetObject(, "Excel.Application")
   ' If no instance, create new instance.
   If Err = ERR_APP_NOTRUNNING Then
      Set xlApp = New Excel.Application
      ' Store current state of UserControl property.
      blnUserControl = xlApp.UserControl
   End If
   With xlApp
      ' Code to automate Excel here.
      ' Check original value of UserControl property.
      If blnUserControl = False Then
         xlApp.Quit
         Set xlApp = Nothing
      End If
   End With
End Sub

Note   Microsoft® PowerPoint®, Microsoft® Outlook®, and Microsoft® FrontPage® have no method of determining if an instance of the Application object has been started by a user or program.

See Also

Office Application Automation | Setting References | Object Variable Declaration | Creation of Object Variables to Automate Another Office Application | Automating the Visual Basic Editor | The Set Statement and the New Keyword in Automation | Single-Use vs. Multi-Use Applications | Using the CreateObject and GetObject Functions | Working with Documents That Contain Startup Code