Understanding Dot Notation

   

This chapter's first section mentioned that the Excel object model is a hierarchy. For example, one object near the top of the hierarchy is the workbook. Workbooks have worksheets that belong to them. In turn, worksheets have rows and columns and cells. (Worksheets have many other objects as well. This book focuses on objects that are important for data management, and the Comment object, for example, isn't critical to managing data.)

In the course of moving data to a database from a worksheet, or to a worksheet from a database, you always need to direct attention to the particular column, row, or cell from which you want to get data or to which you want to write it. And that requirement implies that you need to specify the worksheet where the column, row, or cell is found. Furthermore, if more than one workbook is open when your code runs, you need to specify which workbook you're interested in.

VBA handles all this by means of items and dot notation:

  • When you're dealing with a collection of objects, such as a collection of worksheets in a workbook, you identify a particular object as an item. For example,

     Worksheets("Sheet1") 

    refers to the worksheet named Sheet1. You can also use an object's number instead of its name:

     Worksheets(1) 

    but this can be tricky unless you're absolutely certain that you know which worksheet the number 1 refers to; it's often Sheet1 but it can easily be some other worksheet. Typically you specify an object by means of its name. Sometimes you specify it by its number, especially in the context of loops (see "Using Loops," later in this chapter).

  • You refer to an object that belongs to another object by connecting them with dots. Here's an example:

     Worksheets("Sheet2").ChartObjects("Chart 1") 

    This statement uses dot notation to refer to the embedded chart named Chart 1 on the worksheet named Sheet2: Notice the dot between the first closing parenthesis and the keyword ChartObjects. If you didn't specify where to look for Chart 1, on Sheet2, you'd get an error message. In general, whatever follows a dot belongs to whatever precedes that dot.

In the object model, objects "have" other objects: A workbook has worksheets, a worksheet has cells, cells have borders. But objects also have properties and methods. In your code, you also use dot notation to connect properties and methods to the objects that have them.

For example, you might want to activate Chart 1 on Sheet2. This statement would do that:

 Worksheets("Sheet2").ChartObjects("Chart 1").Activate 

The statement says to activate something. Lots of things can be activated: workbooks, worksheets, charts, and so on. This statement refers to the Activate method as it applies to the chart object named Chart 1 that's found on the worksheet that's named Sheet2.

After your code has activated the chart, you might want it to change the chart's type from whatever it started out as a column chart, perhaps to a line chart with markers. You could use this statement to do that:

 ActiveChart.ChartType = xlLineMarkers 

Here, you begin by referencing the active chart (it's up to you to make sure that there is an active chart at the point that VBA executes the statement). Charts have properties, and one of those properties is the chart's type: column, bar, line, XY (Scatter), and so on. Therefore, the statement determines the chart's type by assigning a particular value, xlLineMarkers, to the ChartType property.

It can help to think of methods and properties in concrete terms. One homely example might be a car. You could consider that a car has several methods, and one of them is the LeftTurn method. You would execute that method to make the car turn left. You could consider that a car has several properties, and one of them is the Color property. You might set its Color property to Red, Blue, Beige whatever color you want that's available.

If you're new to VBA, the distinction between a method and a property might seem a little obscure. Don't worry about it: Over time and with experience, it gets clearer.

TIP

One great way to become familiar with VBA in general and dot notation in particular is to use the macro recorder. Choose Tools, Macro, Record New Macro, and click OK. Then do something relevant to Excel: Open a workbook or delete a row or enter a value or insert a name. Then click the Stop Recording button, switch to the VBE, and examine the code that has been recorded for you. You'll see one way that VBA would automate whatever you just did manually. See "Understanding the Macro Recorder's Code," later in this chapter.


NOTE

Formally, your reference to a collection of objects is to a property. So, in this statement

 Worksheets("Sheet1").Rows(2) 

the reference to Rows is to a property of the worksheet: The collection of the 65,536 rows on that worksheet, and a collection is not an object. But as soon as you specify which row you're referring to (here, that's 2), you've made reference to an object. That's why, when you examine the object model in the VBA Help documentation, you see that Rows is a property of the Worksheet object.




Managing Data with Microsoft Excel
Managing Data with Microsoft Excel
ISBN: 789731002
EAN: N/A
Year: 2003
Pages: 134

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net