Starting Office Applications Using CreateObject

 All applications in Office provide at least one type of object you can create with the CreateObject function: the Application object. However, both Word and Excel provide other types of objects you can create with the CreateObject function. The following table lists all the Office object types you can create:

Application Object type Class
Access Application Access.Application
Excel Application Excel.Application
Excel Worksheet Excel.Sheet
Excel Chart Excel.Chart
Outlook Application Outlook.Application
PowerPoint Application PowerPoint.Application
Word Application Word.Application
Word Document Word.Document

When you create an Excel worksheet or chart, you also implicitly create a new instance of the Excel application. The same holds true when you create a Word document, in which case you create a new instance of the Word application.

CreateObject(ApplicationName.ObjectType) is the syntax for the CreateObject function. ApplicationName is the name of the application that provides the object you want to create, and ObjectType is the type or class of object you'll create. The Class column in the previous table shows the concatenation of the ApplicationName and the ObjectType (with a period in between). The following procedure, added to and executed in a code module in a Visual Basic Editor other than Excel, shows the CreateObject function in the context of Visual Basic for Applications code. The example that follows it describes a similar procedure.

Public appXl As Object

Sub UsingCreateObject()
    Set appXl = CreateObject("Excel.Application")
    Debug.Print appXl.Name
    appXl.Visible = True
End Sub

Create a New Instance of an Object

To create an object for use with Visual Basic for Applications, you have to assign the object that CreateObject returns to an object variable. You do this by completing the following steps:

  1. Start Excel. On the Tools menu, point to Macro, and then click Visual Basic Editor on the submenu.

  2. In the Editor, click Module on the Insert menu.

  3. In the inserted code module, create a new procedure by typing Sub UsingCreateObject and pressing ENTER.

  4. In the first line of the procedure, add the following line of code:

    5. Dim appPPT As Object

TIP
As discussed in Chapter 2, it's a common programming practice to give variable and constant names a prefix indicating the object type. The prefix o can be used generically to denote any type of object variable, but in many cases you'll see a prefix that's more specific, such as app, to denote the Application object.

    With this step, you're declaring the variable appPPT as the generic Object type. This lets you use that variable to represent any type of application object that you create (or any instance of an even wider range of objects). As you'll see in the next example, you should declare each object variable by using the specific object type whenever possible.

  1. Add the following line after the variable declaration statement:

    2. Set appPPT = CreateObject("PowerPoint.Application")

    This assigns to the object variable appPPT the results of the CreateObject function and tells the function what type of object to create (in this case, an instance of the PowerPoint application object). At this point, you can add code to create and manipulate the content of a PowerPoint presentation.

Checking Syntax in Visual Basic

When you press F5 to run your code, Visual Basic first conducts a process called compiling, in which your program is translated into machine language. During this process, Visual Basic checks to see whether a specific object provides the properties and methods you specified in code. Visual Basic also determines whether you've assigned valid values to variables that are declared as a particular type.

If your code has no syntax errors and compiles, Visual Basic knows what your variables are. If you declare a variable as a specific object type that belongs to an application other than the one in which you're writing your code, you must first make a reference to the other application's object library (demonstrated in the following example). When Visual Basic compiles your code, referencing an object library allows it to find all the information about an object, its methods, and its properties.

  1. Add one last line in the procedure:

    2. appPPT.Visible = True

    As discussed at the beginning of this chapter, each Application object in Office has a Visible property allowing you to choose whether or not to display the application window. This allows you to create invisible documents and content that won't distract your program's user and then to display the application when it's needed.

  2. Place the cursor in the procedure and press F5. When the code in this procedure runs in Excel, the PowerPoint application starts and displays the application window on the screen. Once you create an object, you can reference it in code by using the object variable you declared, and you can use any of the properties and methods within the application's object library.

  3. Exit PowerPoint without saving your changes.