Referring to Objects in Excel 2016 VBA Programming
Referring to an object in your VBA code is important because you must identify the object that you want to work with in Excel 2016. After all, VBA can’t read your mind — yet. It’s rumored that the mind-reading object will be introduced in Excel 2019.
You can work with an entire collection of objects in one fell swoop. More often, however, you need to work with a specific object in a collection (such as a particular worksheet in a workbook). To reference a single object from a collection, you put the object’s name or index number in parentheses after the name of the collection, like this:
Notice that the sheet’s name is in quotation marks. If you omit the quotation marks, Excel won’t be able to identify the object (and will assume that it’s a variable name).
If Sheet1 is the first (or only) worksheet in the collection, you can also use the following reference:
In this case, the number is not in quotation marks. Bottom line? If you refer to an object by using its name, use quotation marks. If you refer to an object by using its index number, use a plain number without quotation marks.
What about chart sheets? A chart sheet contains a single chart. It has a sheet tab, but it’s not a worksheet. Well, as it turns out, the object model has a collection called Charts. This collection contains all of the chart sheet objects in a workbook (and does not include charts embedded in a worksheet).
And just to keep things logical, there’s another collection called Sheets. The Sheets collection contains all sheets (worksheets and chart sheets) in a workbook. The Sheets collection is handy if you want to work with all sheets in a workbook and don’t care if they are worksheets or chart sheets.
So, a single worksheet named Sheet1 is a member of two collections: the Worksheets collection and the Sheets collection. You can refer to it in either of two ways:
Navigating through the hierarchy
If you want to work with Excel objects, they are all under the Application object. So start by typing Application.
Every other object in Excel’s object model is under the Application object. You get to these objects by moving down the hierarchy and connecting each object on your way with the dot (.) operator. To get to the Workbook object named Book1.xlsx, start with the Application object and navigate down to the Workbooks collection object:
To navigate farther to a specific worksheet, add a dot operator and access the Worksheets collection object:
Not far enough yet? If you really want to get the value from cell A1 on the first Worksheet of the Workbook named Book1.xlsx, you need to navigate one more level to the Range object:
When you refer to a Range object in this way, it’s called a fully qualified reference. You’ve told Excel exactly which range you want, on which worksheet and in which workbook, and have left nothing to the imagination. Imagination is good in people but not so good in computer programs.
By the way, 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.
Simplifying object references
If you were required to fully qualify every object reference you make, your code would get quite long, and it might be more difficult to read. Fortunately, Excel provides you 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 to
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
Now you’re getting somewhere. Have you guessed the next shortcut? That’s right. If you know the first worksheet is the currently active worksheet, Excel assumes that reference and allows you to just type
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.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 fully qualify your object references.
The With-End With structure 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!