Simplifying object references

If you had to fully qualify every object reference you make, your code would get quite long, and may be more difficult to read. Fortunately, Excel provides you with some shortcuts that can improve the readability (and save you some typing). For starters, 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.xls").Worksheets(1).Range("A1").Value

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

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

Now we're getting somewhere. Have you guessed the next shortcut? That's right, if the first worksheet is the currently active worksheet, then Excel will assume that reference and allow us 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 also be dangerous. What if you only think Book1.xls is the active workbook? You could get an error, or worse, get the wrong value and not even realize it's wrong. For that reason, it's best to fully qualify your object references.

In Chapter 14, I discuss 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!

Was this article helpful?

0 0

Post a comment