Learning about Objects Properties and Methods

When you learn new things, theory can give you the necessary background, but how do you really know what's where? The majority of people think in pictures. To make it easy to understand the Microsoft Excel object hierarchy, the Visual Basic online help offers a diagram of the object model, as shown in the following figures.

Excel Objektmodell
Figure 2-4: Microsoft Excel object hierarchy (Page 1)

Notice that the Application object is positioned at the very top of the diagram. The Application object represents Microsoft Excel itself. Other Excel objects are located at lower levels.

Suppose you want to control the Range object. Before you can control an Excel object, you must create a reference to it. To get to the Range object from the top of the diagram, just follow the lines. Every time you see a line leading to a different level, make a mental note and replace the line

Excel Range Object

Figure 2-6:

Microsoft Excel objects (Worksheet) (Page 3)

Figure 2-5:

Microsoft Excel objects (Workbook) (Page 2)

Figure 2-6:

Microsoft Excel objects (Worksheet) (Page 3)

with a dot operator (a simple period). This way, when you reach the Range object, you will end up with the following reference to the Range object:

Application.Workbook.Worksheet.Range

Using the Microsoft Excel object diagrams, find the paths to other objects, such as Window, Comment, AutoFilter, or ChartArea. Analyzing the object model is a great way to learn about Excel objects. The time you spend here will pay big dividends later when you start writing VBA procedures from scratch. Often, you will be required to specify the exact name of the object that is to be referenced.

Now let's make it even more practical. Suppose you want to delete the contents of cell A4. To do this manually, select cell A4 and press the Delete key on your keyboard. To perform the same operation using Visual Basic, you first need to find out how to make Excel select an appropriate cell. Cell A4, like any other worksheet cell, is represented by the Range object. Visual Basic does not have the Delete method for deleting contents of cells. Instead, use the ClearContents method, as in the following example:

Range("A4").ClearContents

Notice the dot operator between the name of the object and its method. This instruction gets rid of the contents of cell A4. However, how do you make Excel delete the contents of cell A4 located in the first sheet of the Chap02.xls workbook? Let's also assume that there are several Excel workbooks open. If you don't want to end up deleting the contents of cell A4 from the wrong workbook or worksheet, you must write a detailed instruction so that Visual Basic knows where to locate the necessary cell:

Application.Workbooks("Chap02.xls").Worksheets("Sheet1") .Range("A4").ClearContents

The above instruction should be written on one line and read from right to left as follows: Clear the contents of cell A4, which is part of a range located in a worksheet named Sheetl contained in a workbook named Chap02.xls, which in turn is a part of the Excel application. Notice the letter "s" at the end of the collection names: Workbooks and Worksheets. All references to the names of workbooks, worksheets, or cells must be enclosed in quotation marks.

To locate Microsoft Excel object diagrams, choose Help | Microsoft Excel Help in the Microsoft Excel application window. On the Contents tab, click Programming Information | Microsoft Excel Visual Basic Reference | Microsoft Excel Object Model.

In addition to Microsoft Excel objects, you can use the Microsoft Office, Microsoft forms, and DAO and ADO object models. Objects that belong to these libraries can be used in Excel, as well as in other applications that are members of the Microsoft Office family of products. See Chapter 15 for examples of using DAO and ADO object models in accessing the Microsoft Access databases from Excel.

0 0

Post a comment