Accessing the Object Library

Vertex42 The Excel Nexus

Professional Excel Templates

Get Instant Access

For VBA to manipulate a program — or a document within a program — VBA first needs to have access to that program's object library. You might envision VBA as sort of a steering wheel that can control any program to which it has access (through an object library), as in Figure 14-1.




Figure 14-1:

VBA can control any program through that program's object library.




Figure 14-1:

VBA can control any program through that program's object library.

VBA steers the action

To write code for an Office application program, you first need to set a reference to that program's object library. To do so, starting from Microsoft Access, follow these steps:

1. In Access, open the database that contains objects to share with other programs and open the VBA Editor.

2. Choose ToolsOReference from the VBA Editor menu bar.

3. From the list of available references, choose the libraries for the applications you want to program.

For example, in Figure 14-2, we added references to Excel (Microsoft Excel 12.0 Object Library) and Word (Microsoft Word 12.0 Object Library).

Figure 14-2:

Choose object libraries in the References dialog box.

References 14 Automation Examples g

Available References:

0 Visual Basic For Applications 0 Microsoft Access 2007 (Beta) Object Library 0 OLE Automation 0 Microsoft ActiveX Data Objects 2.1 Library 0 Microsoft Office 2007 Access database engine Objecl 0 Microsoft Visual Basic for Applications Extensibility 5.2 0 Microsoft Excel 12.0 Object Library 0 Microsoft Office 12.0 Object Library 0 Microsoft Outlook 12,0 Object Library 0Microsoft Word 12.0 Object Library ]

□ IA5 Helper COM Component 1,0 Type Library

□ IAS RADIUS Protocol 1.0 Type Library

□ Acrobat Access 3.0 Type Library PI AcroIEHeber 1,0 Tvoe Librarv



Microsoft Word 12,0 Object Library

Location : C : \Program Files^Microsoft Of f ice 2007\0f f ice 12\MS WORD. ( Language: Standard

Exploring a program's object model

After you set a reference to a program's object model, you can explore its exposed objects, properties, and methods through the Object Browser. In the VBA Editor, just press F2 or choose ViewOObject Browser. To limit the display to a given program's objects, choose that program's name from the Project/Library drop-down list. For example, in Figure 14-3, Excel is selected from the Project/Library drop-down list. The classes and members in the columns beneath this list refer to Microsoft Excel and any data that might be in the open Excel worksheet.

\ In the Object Browser, classes mean objects, collections, and such, whereas ) members mean properties, methods, and events of (whatever is highlighted J in the Classes pane).

What if you don't have Word or Excel or Outlook?

Automation between Microsoft Office programs works with only the programs installed on your computer. If you don't have a given program (like Microsoft Outlook) installed, you can't load its object library or control it through VBA.

Things can get confusing when you copy a database (an ACCDB file) that contains VBA

code to a different computer. Any code that refers to Word, Excel, Outlook, or PowerPoint fails if the current computer doesn't have those programs installed. In other words, VBA can't create those programs if they're missing. VBA can use those programs only if they already exist on the current computer.

For more goods on the Object Browser, see Chapter 2.

Each Office application exposes a lot of objects to VBA. Even if you limit the Object Browser to show just one program's model, you still end up with a zillion names of things. We don't have enough room in this book to define all those things. You just have to learn how to get the information you need (whatever that might be) when you need it. In the Object Browser, that generally involves clicking the name you need help with and then clicking the ? (Help) button.

Figure 14-3:

Viewing classes and members of the Excel object library.

fjy Object Browser



'1 *M f

j Excel

1 WjT


1 Class

1 Member |

H\ Excel


gl? Excel4MacroSheets !A

Dl\ Excel

0 Workbook

ié? Excel4MacroSheets C

H\ Excel


ëû1 Excel8CompatibilityM

U\ Excel


& ExecuteExcel4Macro

U\ Excel


© xlExceM 2 ¡v|


Members of'Application'

9 <globals>



Above Ave rage


& DecimalSeparator

i£jl Action

^ DefaultFilePath

0 Actions

i^1 DefaultSaveFormat

© Addln

& DefaultSheetDirection

© Addlns

IÉ? DefaultWebOptions

© Adjustments

& DeferAsyncQueries

0 AllowEditRange


0 AllowEditRanges

El1 Dialogs



0 Areas

Ei1 DisplayClipboardWindow

0 AutoCorrect


© AutoFilter

iE? DisplayDocumentActionTaskPane

© AutoRecover

ii£? DisplayDocumentlnformationPanel

© Axes


IÉ? DisplayE.rel4Menu;


Class Application

Meet the Application object

Different application programs expose different object models to VBA, but all programs have in common an Application object (with a capital 4). The program's Application object exposes all that program's collections and objects to VBA.

If a document is open in the program, the document's objects are also exposed to VBA. For example, when VBA opens an Excel workbook, Excel exposes its own capabilities to VBA through its Application object. Every cell in the workbook is also exposed. Basically, VBA can do anything in the workbook that a person sitting at the workbook could do from the Excel Ribbon.

Connecting to other programs

After you set a reference to an external program's object library, you can create instances that you program in VBA. An instance is basically the same idea as an open program window. For example, when you start Microsoft Internet Explorer on your computer, you're creating an instance of Internet Explorer. If you right-click a link and choose Open in New Window, a new, separate Internet Explorer window opens to show the new page. Now you have two instances of Internet Explorer open, each showing a different Web page.

Before you can create an instance of a program, you have to declare an object variable that will become the name used by VBA to refer to the program. The object variable name can be any name you like. Just try to think of a short, simple name that's meaningful. The syntax for declaring an object variable that refers to an external open program is

Dim objectVariable As New program.Application

In the syntax, objectVariable is the object variable name, and program is a reference to one of the Office programs: Word, Excel, Outlook, or PowerPoint. The .Application part refers to the program's Application object of that program. The New keyword is optional but recommended because it ensures that the object creates a new instance of the program. Here are some examples of declaring object variables for each of the Office programs:

Dim XL As New Excel.Application Dim Wrd As New Word.Application Dim Olk As New Outlook.Application Dim Ppt As New PowerPoint.Application

You must set a reference to a program before writing a Dim statement to declare an instance of the program.

After you declare an object variable to refer to an open instance of a program, you can then open that program (and any document) so that your VBA code has access to all the program's objects. The syntax for opening a program is

Set objectVariable As CreateObject("program.Application")

where objectVariable is the same as the name you specified in the Dim statement and program is the name of the application program: Excel, Word, PowerPoint, or Outlook. Referring to the earlier Dim statements, the Set statements that you use for each defined object variable are


XL =





= CreateObject(




= CreateObject(




= CreateObject(


We use short names for our object variables here: XL for Excel, Wrd for Word, Olk for Outlook, and Ppt for PowerPoint. You can use any names you want. We kept ours short just to save space here.

Anyway, that's the basic procedure for making the connection to an external program. To review and summarize, the basic procedure is

Was this article helpful?

0 -1


Post a comment