Excel VBA Programming For Dummies. Dick Kusleika

Excel VBA Programming For Dummies

hierarchy

The Application object is at the very top of the hierarchy in the object model. It represents Excel itself and every other object is a child or grandchild of the Application object. The following are some of the more useful objects contained just below Application in the hierarchy:

Addin

Window

Workbook

WorksheetFunction

Each object contained in the Application object can contain other objects. For example, the following are some objects that can be contained in a Workbook object:

Chart (which is a chart sheet)

Name

VBProject

Window

Worksheet

In turn, each of these objects can contain still other objects. Consider a Worksheet object, which is contained in a Workbook object, that is contained in the Application object. Some of the objects that can be contained in a Worksheet object are

Comment

Hyperlink

Name

PageSetup

PivotTable

Range

If you want to do something with a range on a particular worksheet, you might find it helpful to visualize that range in the following manner:

Range ⇒ contained in Worksheet ⇒ contained in Workbook ⇒ contained in Excel

Excel has more objects than you can shake a stick at. Even power users would get overwhelmed if they tried to learn every object in the object model. The good news is that you don’t have to do anything with most of these objects. You could go your whole VBA programming life and never need to use the Excel4MacroSheet object. You only need to know the objects for the problem your working on and most of the time the macro recorder tells you everything you need.

Referring to objects

Referring to an object is important because you must identify the object with which you want to work. After all, VBA can’t read your mind — yet. The Application object, being the top dog, is easy. Just type Application and a period (called a dot operator) to get to its properties and methods. For example, if you wanted to change Excel’s behavior so that the cursor doesn’t move when you press Enter, you could write the following code:

Application.MoveAfterReturn = False

Some of the Application object’s properties return other objects. Just like the MoveAfterReturn property return a value (either True or False), a property can also return an object. AutoCorrect is an object in the object model that’s just below the Application object. The way you refer to the AutoCorrect object is by using the AutoCorrect property. Yes, the property and the object it returns have the same name. If you want to tell Excel to fix your typing when you leave Caps Lock on, you would do so with the following code:

Application.AutoCorrect.CorrectCapsLock = True

In this case, CorrectCapsLock is a property of the AutoCorrect object and you are able to address that object via the AutoCorrect property of the Application object.

In the previous section, I wrote that cars are similar to Excel objects because they have properties like color and number of pistons. Cars also have steering wheels and steering wheels are objects, too. They’re just one rung down the car object hierarchy. If a car were a computer program, it would have a property called SteeringWheel that you would use to access the SteeringWheel object.

Workbook names also have a dot to separate the filename from the extension (for example, Book1.xlsx). That’s just a coincidence. The dot in a filename has nothing at all to do with the dot operator referred to a few paragraphs ago.

Using Collection objects

Now you can work with the Application object and any object that’s just below it via one of its properties. To get another level below that you need collection objects.

Collection objects are objects that give you access to single objects contained inside them. In many cases, the collection object’s name is the plural of the single object’s name. For example, if you want to work with a specific Workbook object, you need to go through the Workbooks collection object to get there.

Here are a few examples of commonly used collections:

Workbooks: A collection of all currently open Workbook objects

Worksheets: A collection of all Worksheet objects contained in a particular Workbook object

Charts: A collection of all Chart objects (chart sheets) contained in a particular Workbook object

Sheets: A collection of all sheets (regardless of their type) contained in a particular Workbook object. To get to a Workbook object, you can start with the Application object like in the following code: Application.Workbooks.Item("Book1").Path

Just like Application had an AutoCorrect property to get to the AutoCorrect object, it has a Workbooks property to get to the Workbooks collection object. All collection objects have an Item property to get to a singular instance of an object they collect. In this example, the Item property returns a Workbook object because that’s the type of object in the Workbook collection.

A collection’s Item property takes one argument. That argument can be a string (enclosed in double quotes) or an integer. If you provide a string argument, Item returns an object whose name matches the string. In the example above, Book1 is the name of the workbook returned. If you provide an integer argument, Item returns the object whose position in the collection matches that number. The following code returns the first worksheet of the second workbook:

Application.Workbooks.Item(2).Worksheets.Item(1)

You might not always know how the objects are ordered in the collection. With worksheets, they are ordered the same way as in Excel. The left-most worksheet is the first worksheet in the collection. But workbooks aren’t so straightforward. There are hidden workbooks like Add-Ins and the Personal Macro Workbook that are in the collection but not visible in Excel.

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

Скачать книгу