To point to a specific object in VBA, you can traverse the object model. For example, to get to cell A1 on Sheet 1, you can enter this code:
Application.ThisWorkbook.Sheets("Sheet1").Range("A1").Select
In most cases, the object model hierarchy is understood, so you don't have to type every level. Entering this code also gets you to cell A1 because Excel infers that you mean the active workbook and the active sheet:
Range("A1").Select
Indeed, if you have your cursor already in cell A1, you can simply use the ActiveCell object, negating the need to spell out the range.
Activecell.Select
Understanding collections
Many of Excel's objects belong to collections. Your house sits within a neighborhood, for example, which is a collection of houses called a neighborhood. Each neighborhood sits in a collection of neighborhoods called a city. Excel considers collections to be objects themselves.
In each Workbook object, you have a collection of Worksheets. The Worksheets collection is an object that you can call upon through VBA. Each worksheet in your workbook lives in the Worksheets collection.
If you want to refer to a worksheet in the Worksheets collection, you can refer to it by its position in the collection as an index number starting with 1 or by its name as quoted text. If you run these two lines of code in a workbook that has only one worksheet and that worksheet is called MySheet, they both do the same thing:
Worksheets(1).Select Worksheets("MySheet").Select
If you have two worksheets in the active workbook that have the names MySheet and YourSheet, in that order, you can refer to the second worksheet by typing either of these statements:
Worksheets(2).Select Worksheets("YourSheet").Select
If you want to refer to a worksheet in a workbook called MySheet in a particular workbook that is not active, you must qualify the worksheet reference and the workbook reference. Here's an example:
Workbooks("MyData.xlsx").Worksheets("MySheet").Select
Understanding properties
Properties are essentially the characteristics of an object. Your house has a color, a square footage, an age, and so on. Some properties can be changed, like the color of your house. Other properties can't be changed, like the year your house was constructed.
Likewise, an object in Excel, like the Worksheet object, has a sheet name property that can be changed, and a Rows.Count row property that cannot.
You refer to the property of an object by referring to the object and then the property. For instance, you can change the name of your worksheet by changing its Name property.
In this example, you are renaming Sheet1 to MySheet:
Sheets("Sheet1").Name = "MySheet"
Some properties are read-only, which means you can't assign a value to them directly—for instance, the Text property of a cell. The Text property gives you the formatted appearance of value in a cell, but you cannot overwrite or change it.
Some properties have arguments that further specify the property value. For instance, this line of code uses the RowAbsolute and ColumnAbsolute arguments to return the address of cell A1 as an absolute reference ($A$ 1).
MsgBox Range("A1").Address(RowAbsolute:=True, ColumnAbsolute:=True)
Specifying properties for the active object
When you're working with Excel, only one workbook at a time can be active. In that workbook, only one sheet can be active. And if the sheet is a worksheet, one cell is the active cell (even if a multicell range is selected). VBA knows about active workbooks, worksheets, and cells, and it lets you refer to these active objects in a simplified manner.
This method of referring to objects is often useful because you won't always know the exact workbook, worksheet, or range on which you want to operate. VBA makes object referencing easy by providing properties of the Application object. For example, the Application object has an ActiveCell property that returns a reference to the active cell. The following instruction assigns the value 1 to the active cell:
ActiveCell.Value = 1
In the preceding example, we omitted the reference to the Application object and to the active worksheet because both are assumed. This instruction will fail if the active sheet isn't a worksheet. For example, if VBA executes this statement when a chart sheet is active, the procedure halts and you get an error message.
If a range is selected in a worksheet, the active cell is a cell within the selected range. In other words, the active cell is always a single cell (never a multicell range).
The Application object also has a Selection property that returns a reference to whatever is selected, which may be a single cell (the active cell), a range of cells, or an object such as ChartObject, TextBox, or Shape.
Table 2.1 lists the other Application properties that are useful when working with cells and ranges.
TABLE 2.1 Some Useful Properties of the Application Object
| Property | Object Returned |
ActiveCell
|
The active cell. |
ActiveChart
|
The active chart sheet or chart contained in a ChartObject on a worksheet. This property is Nothing if a chart isn't active.
|
ActiveSheet
|
The active sheet (worksheet or chart sheet). |
ActiveWindow
|
The active window. |
ActiveWorkbook
|
The active workbook. |
Selection
|
The object selected. It could be a Range object, Shape, ChartObject, and so on.
|
ThisWorkbook
|
The workbook that contains the VBA procedure being executed. This object may or may not be the same as the ActiveWorkbook object.
|
The advantage of using these properties to return an object is that you don't need to know which cell, worksheet, or workbook is active, and you don't need to provide a specific reference to it. This allows you to write VBA code that isn't specific to a particular workbook, sheet, or range. For example, the following instruction clears the contents of the active cell, even though the address of the active cell isn't known:
ActiveCell.ClearContents
The example that follows displays a message that tells you the name of the active sheet:
MsgBox