Читать книгу Excel VBA Programming For Dummies - Dick Kusleika - Страница 74

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!