Читать книгу Excel 2019 Power Programming with VBA - Michael Alexander, Dick Kusleika - Страница 109

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 ActiveSheet.Name

If you want to know the name and directory path of the active workbook, use a statement like this:

MsgBox ActiveWorkbook.FullName

If a range on a worksheet is selected, you can fill the entire range with a value by executing a single statement. In the following example, the Selection property of the Application object returns a Range object that corresponds to the selected cells. The instruction simply modifies the Value property of this Range object, and the result is a range filled with a single value.

Selection.Value = 12

If something other than a range is selected (such as a ChartObject or a Shape), the preceding statement generates an error because ChartObject and Shape objects don't have a Value property.

The following statement, however, enters a value of 12 into the Range object that was selected before a non-Range object was selected. If you look up the RangeSelection property in the Help system, you find that this property applies only to a Window object.

ActiveWindow.RangeSelection.Value = 12

To find out how many cells are selected in the active window, access the Count property. Here's an example:

MsgBox ActiveWindow.RangeSelection.Count