Understanding Office Code: Programmatic Object Models

To take full advantage of the ways you can automate Microsoft Office features by writing code, you need to understand the Office programmatic object models. An object model describes the relationships between application features that can be extended or automated using code. These features are exposed through type libraries, which are contained in files such as a dynamic-link library (DLL) files, executable (EXE) files, object library files (files with the extension .olb), or type library files (files with the extension .tlb). Some common type libraries are the Microsoft Excel Type Library (contained in the file Excel.exe) and the Microsoft Access Object Library (contained in the file Msacc.olb).

Object models include several components, namely classes (which include objects, collections, and types) and members (which include properties, methods, events, and constants).

• An object is an application component or feature. Examples of an object are an Excel Worksheet object, an Access Report object, and so on.

• A collection is a group of objects. Collections include the Excel Worksheets collection, the Access Reports collection, and so on.

• A property is a characteristic of an object or a collection. The Excel Worksheet object has a Visible property, which indicates whether the Worksheet object is visible or hidden.

• A method is an action that an object or collection can perform. One example is the Worksheets collection’s Add method, which adds a Worksheet object to a Workbook object.

• An event is an action that a user or an application performs on an object or a collection. For instance, the Workbook object’s Open event is triggered when the workbook file the object refers to is opened.

• A constant is text that substitutes for an integer. For example, the Excel constant xlFillCopy represents the integer 1. Computers think in terms of numbers, but we use constants as substitutes for numbers because words are easier for us to remember. Constants can be organized into groups of types.

The Visual Basic Editor

Excel and Access support the Visual Basic Editor, a tool that you can use to attach VBA macros or other code files (also called modules) to workbooks and databases. The code that you write runs in response to application or user actions such as a file opening, a mouse click on a particular screen location, and so on.

To display the Visual Basic Editor when you’re working in an Office application, point to Macro on the Tools menu and then click Visual Basic Editor. The Visual Basic Editor consists of the following major components:

• The Project Explorer, which allows you to manage the VBA code accompanying an Office document. To display the Project Editor, click Project Explorer on the View menu.

• The Properties window, which allows you to view and modify VBA project and code properties such as code file names. You can display the Properties window by clicking Properties Window on the View menu.

• The Code window (on the View menu, click Code), which is the window in which you enter VBA code.

• The Immediate Window (on the View menu, click Immediate Window), which allows you to set and retrieve code properties while you’re testing VBA code.

• The References dialog box (on the Tools menu, click References), which you use to add references to your VBA code to features and functions in other software applications and library files.

• The Object Browser (on the View menu, click Object Browser), which displays classes, properties, events, and so on from object libraries and procedures in your VBA project. You can also use the Object Browser to inspect the features and functions of other applications. The Object Browser is described in more detail in the next section.

You’ll see these features illustrated and used in some of the procedures that follow. For more information on how to use the Visual Basic Editor and its components, see the Visual Basic Editor online help.

Note

Data analysis solutions targeting the Office Web Components and Microsoft Data Analyzer use different coding approaches. Because the Office Web Components are hosted on Web pages, you use a Web scripting language such as Microsoft Visual Basic Scripting Edition (VBScript). Data Analyzer views can be hosted in a variety of solutions, such as Visual Basic forms, VBA UserForms, or Web pages. In these cases, the host application determines whether you can use a programming language such as Visual Basic, Visual Basic for Applications, or VBScript.

The Object Browser and Online Help

The Object Browser and the Office VBA Language Reference are tools you can use to help you understand the various Office object models. To display the Object Browser, first open the Visual Basic Editor (point to Macro on the Tools menu, and then click Visual Basic Editor). On the View menu, click Object Browser. The Object Browser is shown in Figure 11-1.

Figure 11-1: The Object Browser.

To reduce the amount of information the Object Browser displays, click a single type library in the Project/Library list near the top of the browser. The classes (objects, collections, and types) for that type library are displayed in the Classes pane. As you click an item in the Classes pane, the members (properties, methods, events, and constants) for that item are displayed in the Members pane. You can also type the name of an item you’re looking for in the Search Text box, click Search, and then click an item in the Search Results pane.

To view the Office VBA Language Reference help topic associated with a class or member, click the class or member and then press F1. You can see the topic for the Excel Application object in Figure 11-2.

Figure 11-2: The Office VBA Language Reference online help provides information about the items in an object model.

From this topic, you can access help topics for all the Excel Application object’s members and child objects. You can also understand what the Excel Application object represents, learn how to use the Excel Application object in your code, and read usage remarks.

To change the entries in the Project/Library list, on the Tools menu, click References. In the References dialog box, shown in Figure 11-3, select or clear the check boxes for the type libraries you want to add or remove.

Figure 11-3: Referencing projects and programmatic libraries (object models).

Note

Selecting items in the References dialog box increases the size of the Office file associated with the VBA project. If you are just exploring a type library’s classes and members, be sure to clear the type library’s corresponding check box when you are finished exploring it.

Working with the Application Object

Object models are organized into hierarchies of objects and collections, similar to family trees. Objects and collections can have parent and child objects and collections. To navigate through an object model, you usually start with the object model’s Application object and then move through the object model from there. To start with the Application object, you declare (or dimension, using the Dim statement) an Application object, as shown in the following code snippet:

... Dim acApp As Access.Application ’ For Access. Dim xlApp As Excel.Application ’ For Excel. ...

In this code, I’ve used the standard letters ac and xl, along with the letters App, as the names of the variables (a variable is a computer memory location that stores objects) corresponding to an instance of each application’s Application object (acApp and xlApp). Building on the Excel example above, you can reference the active Excel workbook with code similar to the following:

... Dim xlApp As Excel.Application Dim objWkb As Excel.Workbook Set xlApp = Excel.Application Set objWkb = xlApp.ActiveWorkbook ...

Rounding out this example, you can write code to list the names of the active workbook’s worksheets as follows:

Public Sub WorksheetNames() Dim xlApp As Excel.Application Dim objWkb As Excel.Workbook Dim objWks As Excel.Worksheet Dim strMsg As String Set xlApp = Excel.Application Set objWkb = xlApp.ActiveWorkbook strMsg = "This workbook’s worksheets are named:" & vbCrLf For Each objWks In objWkb.Sheets strMsg = strMsg & objWks.Name & vbCrLf Next objWks MsgBox Prompt:=strMsg Set objWkb = Nothing Set xlApp = Nothing End Sub 

Here’s how the code works.

• The lines Public Sub WorksheetNames() and End Sub denote the beginning and end of the WorksheetNames procedure.

• The lines starting with the Dim keyword instruct the computer to reserve locations in memory to store program data and manipulate Excel features. The Excel.Application object (xlApp) represents an Excel application. The Excel.Workbook object (objWkb) represents an Excel workbook. The Excel.Worksheet object (objWks) represents a worksheet in a workbook. The String object (strMsg) represents some text that is constructed later in the procedure.

• The lines starting with the Set keyword instruct the computer to assign the reserved memory locations to Excel features. The xlApp object is assigned to the running Excel application. The objWkb object is assigned to the active workbook in the running Excel application.

• The strMsg object is set to the text “This workbook’s worksheets are named:” followed by a carriage return.

• The For Each…Next keywords are used to iterate, or loop, through a collection of objects. In this case, the Workbook object’s Sheets property provides access to all the workbook’s worksheets. Each worksheet’s name (found on the worksheet’s tab and represented in the code as objWks.Name) is appended to the strMsg text, followed by a carriage return.

• The MsgBox function displays the strMsg text on the screen.

• Finally, to manage computer memory wisely, at the end of the subroutine, when the memory locations are no longer needed, the memory locations’ contents are emptied and released to other computer processes by setting the object variables, such as objWkb, to Nothing. Then the procedure ends.

Your Turn

In this exercise, you’ll run and test the WorksheetNames subroutine presented above.

1. Start Excel and create a new, blank workbook.

2. Open the Visual Basic Editor (on the Tools menu, point to Macro and then click Visual Basic Editor).

3. On the View menu, click Project Explorer.

4. Expand the project matching the name of your workbook; for example, VBAProject (Book 1).

5. Open the Microsoft Excel Objects folder.

6. Double-click the ThisWorkbook module.

7. Type the contents of the WorksheetNames procedure into the Code window (which is next to the Project Explorer).

8. Click anywhere between the lines of code Public Sub WorksheetNames() and End Sub.

9. On the Run menu, click Run Sub/UserForm. The workbook’s worksheet names are displayed. Figure 11-4 shows the message box displayed for a default worksheet.

   
   Figure 11-4: Results of running the WorksheetNames subroutine on a default worksheet.

Now that you are beginning to understand how Office solutions are created with macros, procedures, and an object model, you can start creating some of your own data analysis solutions. In the sections that follow, I’ll show you some code that performs a data analysis task and explain how the code works. Try running the code samples, learn how the code works, and then customize and add to the code for your own needs.