Excel VBA Programming For Dummies. Dick Kusleika

Чтение книги онлайн.

СКАЧАТЬ align="center">

      Considering generic collection objects

      The Workbooks object contains Workbook objects and the Worksheets object contains Worksheet objects. It all seems very clean and simple. But there also collection objects that contain objects that are similar but not exactly the same. The example of this type of object that you’ll see most often is the Sheets collection object. The Sheets object contains both Worksheet objects and Chart objects. Sometimes you want to work with every sheet in a workbook regardless of its type. In that case, you would use the Sheets object to get at both types of sheets. The following example demonstrates how to access a worksheet named Sheet1 using either the Worksheets object or the Sheets object:

       Application.Workbooks.Item(1).Worksheets.Item("Sheet1")Application.Workbooks.Item(1).Sheets.Item("Sheet1")

      Simplifying object references

      All the examples in this section so far have been fully qualified references. A fully qualified reference starts with the Application object and traverses the hierarchy all the way down to the object you want. They are unambiguous, but if you were required to fully qualify every object reference you make, your code would be quite long, more difficult to read, and your keyboard would wear out too fast. Fortunately, Excel provides some shortcuts that can improve the readability and save you some typing.

For starters, objects have default properties. If you want to use the default property, you don’t have to type and VBA will know what you mean. The star of the default property show is the Item property. Item is the default property for every collection object making the following code equivalent:

       Application.Workbooks.Item("Book1.xlsx").Worksheets.Item(2).Range("A1").ValueApplication.Workbooks("Book1.xlsx").Worksheets(2).Range("A1").Value

      While it’s important to understand the object hierarchy and how the Item property works, you almost never see Item used in code.

      The next shortcut is that the Application object is always assumed. There are only a few cases when it makes sense to type it. Omitting the Application object reference shortens the example from the previous section to

       Workbooks("Book1.xlsx").Worksheets(2).Range("A1").Value

      That’s a pretty good improvement. But wait, there’s more. If you’re sure that Book1.xlsx is the active workbook, you can omit that reference, too. Now you’re down to

       Worksheets(2).Range("A1").Value

      Finally, you’re getting somewhere. Have you guessed the next shortcut? That’s right. If you know the second worksheet is the currently active worksheet, Excel assumes that reference and allows you to just type

       Range("A1").Value

      

Contrary to what some people may think, Excel does not have a Cell object. A cell is simply a Range object that consists of just one element.

      The shortcuts described here are great, but they can be dangerous. What if you only think Book1.xlsx is the active workbook? You could get an error, or worse, you could get the wrong value and not even realize it’s wrong. For that reason, it’s often best to qualify your object references enough to be safe. You almost never need the Application object in your reference, but you may need the Workbook object.

      Chapter 14 discusses the With-End With structure, which helps you fully qualify your references but also helps to make the code more readable and cuts down on the typing. The best of both worlds!

Diving into Object Properties and Methods

      Although knowing how to refer to objects is important, you can’t do anything useful by simply referring to an object (as in the examples in the preceding sections). To accomplish anything meaningful, you must do one of two things:

       Read or modify an object’s properties.

       Call a method of action to be used with an object.

A FAST-FOOD TAKE ON OBJECTS, PROPERTIES, AND METHODS

      Here’s an analogy comparing Excel to a fast-food chain that may help you understand the relationships among objects, properties, and methods in VBA.

      The basic unit of Excel is a Workbook object. In a fast-food chain, the basic unit is an individual restaurant. With Excel, you can add a workbook and close a workbook, and all the open workbooks are known as Workbooks (a collection of Workbook objects). Similarly, the management of a fast-food chain can add a restaurant and close a restaurant, and all the restaurants in the chain can be viewed as the Restaurants collection (a collection of Restaurant objects).

      An Excel workbook is an object, but it also contains other objects such as worksheets, chart sheets, VBA modules, and so on. Furthermore, each object in a workbook can contain its own objects. For example, a Worksheet object can contain Range objects, PivotTable objects, Shape objects, and so on.

      Continuing with the analogy, a fast-food restaurant (like a workbook) contains objects such as the Kitchen, DiningArea, and Tables (a collection). Furthermore, management can add or remove objects from the Restaurant object. For example, management may add more tables to the Tables collection. Each of these objects can contain other objects. For example, the Kitchen object has a Stove object, VentilationFan object, Chef object, Sink object, and so on.

      So far, so good. This analogy seems to work.

      Excel’s objects have properties. For example, a Range object has properties such as Value and Address, and a Shape object has properties such as Width, Height, and so on. Not surprisingly, objects in a fast-food restaurant also have properties. The Stove object, for example, has properties such as Temperature and NumberofBurners. The VentilationFan has its own set of properties (TurnedOn, RPM, and so on).

      Besides properties, Excel’s objects also have methods, which perform an operation on an object. For example, the ClearContents method erases the contents of a Range object. An object in a fast-food restaurant also has methods. You can easily envision a ChangeThermostat method for a Stove object or a SwitchOn method for a VentilationFan object.

      In Excel, methods sometimes change an object’s properties. The ClearContents method for a Range changes the Range’s Value property. Similarly, the ChangeThermostat method on a Stove object affects its Temperature property. With VBA, you can write procedures to manipulate Excel’s objects. In a fast-food restaurant, the management can give orders to manipulate the objects in the restaurants (“Turn the stove on and switch the ventilation fan to high”).

      The next time you visit your favorite fast-food joint, just say, “Use the Grill method on a Burger object with the Onion property set to False.”

      Setting object properties

      Every object has properties. You can think of properties as characteristics that describe СКАЧАТЬ