To work with an application other than the one where you execute your Visual Basic code, you must first set an object variable so that it represents the application you want to use. To do this, you can either create a new instance of the application or access an existing instance of an application (one that's currently running). When you start an instance of a Word, Excel, PowerPoint, Access, or Outlook application by using the CreateObject or GetObject function or the New keyword, the application window isn't displayed on the screen or the Windows taskbar.
Each Application object in Office, with the exception of Outlook, provides a Visible property that allows you to display the application window after you create the application. In many Visual Basic for Applications programming scenarios, you create an Application object first, and then, before displaying the application window, you manipulate documents that you open or create. Once you finish, you display the application window with the document or documents. This technique allows you to make changes without distracting your program's user.
As the accompanying table shows, when you use CreateObject or the New keyword to create an instance of an Office application, Visual Basic creates a new instance of the application on the system for all applications except PowerPoint and Outlook. This means that if Word or Excel is already running and visible, CreateObject or the New keyword actually loads another instance of Word or Excel. For example, two Windows processes will have the Word executable loaded: the one visible and the other started using CreateObject or the New keyword. If you exit the visible instance, the other instance of Word remains. You must explicitly close the instance created by your code.
| Application | New instance created when using CreateObject or New keyword |
|---|---|
| Access | Yes |
| Excel | Yes |
| Outlook | No |
| PowerPoint | No |
| Word | Yes |
In the case of PowerPoint and Outlook, a new instance isn't created and CreateObject and the New keyword attaches to an existing instance of these applications (if either one is running).
NOTE
When you use the CreateObject function to create a new instance of an Application object, you assign the Application object returned by the function to an object variable. In this case, and in the case where the New keyword is used in a variable declaration to create a new instance of an Application object, you need to declare the object variable at either a procedure, module, or public level. For example, as described in Chapter 2, if the object variable is declared at a module or public level, the application associated with the Application object remains loaded even after the procedure that creates the application is executed.