Controlling an Office Application from VBA

So far in this chapter, we've focused on the components inside VBA—projects, modules, custom dialog boxes, controls, events, and the like. Ultimately, however, almost every VBA program interacts with the underlying application: For example, you usually write a VBA/Word macro to perform some sort of action on a Word document. That isn't an absolute requirement—you can write a VBA/Excel macro that doesn't interact with any workbooks—but in most cases you will want your program to control the underlying application.

That's where the object model comes in. When a VBA/Word macro controls Word, it does so by using the Word object model. When a VBA/PowerPoint macro changes a slide, it uses the PowerPoint object model.

→ For an overview of the object models, see "Using Object Models".

Application object models can be enormously complex: It would take at least 100 pages to describe just the highlights of the Word object model, for example—and you could devote an encyclopedia to its many nuances. But if you understand the application itself, you've won half the battle. In fact, it's far, far easier for a Word, Excel, PowerPoint, Outlook, Publisher, or FrontPage expert to learn VBA than for a VB (or VBA) expert to learn Word, Excel, PowerPoint, Outlook, Publisher, or FrontPage.

To get started with the object model in a particular application, look through the Object Browser, and try typing a few commands. If you get stuck, press F1 and VBA's context- sensitive help will appear, with extensive details and a few working samples.

That said, here's a thumbnail introduction to the object models in each of the Office applications. We'll go over the fundamental ways of using these object models in the next chapter. Note that initial capitals are used to identify the names of objects and methods.

→ For a quick comparison of object models, see "Opening, Closing, and Creating New Documents".

• The foundation of all Word objects is the document: You can add them (that is, create a new document), save them, and open, close, and print them. The current active document is known as the ActiveDocument. When you get inside a document, you're most likely to use the Selection object (in other words, whatever text, pictures, tables, and so on, are currently selected).

• Excel, on the other hand, starts with a workbook. Inside an individual workbook, you'll most likely run with the Sheets object (or Worksheets if you want to limit yourself to just worksheets). Within Sheets, Ranges do most of the work.

• Presentations rule in PowerPoint. Inside a presentation, there are Slide objects, and the TextFrame and TextRange objects on the slides hold the text. Master objects control the Slide, Title, Handout, and Notes masters.

• Publisher bears many similarities to Word, except there's more emphasis on the predefined objects available in Publisher—for example, Layout guides and Connector formats.

• Outlook objects run the gamut of Outlook applications, from contacts to e-mail to Calendar items to to-do lists.

• FrontPage has two independent object models, one for the editor itself, and the other for HTML constructs (tags and the like).

• Access, always the oddball, doesn't have a Project object (although there is an OpenAccessProject command). Instead, it deals with Forms, Reports, Modules, and Screens—where the Screen object is just the currently active form, report, or control.

There are also object models for Data Access Objects (DAO), and even the VBA Editor itself. The DAO and VBA Editor object models are robust, and a skilled VBA programmer can practically work miracles with them.