Convenient Excel Object Properties

The Application object has a number of properties that conveniently return Excel objects of interest. For example, you often need a way to refer to the currently selected range, the active workbook, or the workbook that the module resides in (this may or may not be equal to the active workbook). The Application object has properties that return objects representing all of these and more. Table 5.1 lists the properties that are useful for returning particular Excel objects.

Table 5.1: Properties Returning Excel Objects of Interest

Property

ActiveCell

ActiveChart

ActivePrinter

ActiveSheet

ActiveWindow

ActiveWorkbook

Selection

ThisCell ThisWorkbook

Returns

Range

Chart

String

Sheet

Window

Workbook

Various

Range Workbook

Represents

The active cell in the active window.

The active chart.

The name of the active printer.

The active sheet in the active workbook.

The active window.

The workbook in the active window.

The selected object in the active window. The object returned depends on what is selected. Usually a range is selected so a Range object is returned.

The cell from which a user-defined function is being called.

The workbook containing the VBA code.

Many beginners use these properties almost exclusively for some reason, along with the Activate method that activates various objects of interest. Perhaps it is because the macro recorder uses these a lot, and people learn from the code that the macro recorder generates. In any event, the practice of using Activate is inefficient because the Activate method is an unnecessary performance hit since you don't need to activate objects to use them, as you'll see in later chapters. That said, use these properties when you need to, but know that if you're using these properties in conjunction with the Activate method, you are making things tougher on yourself.

Out of all of these properties, I find myself using the ThisWorkbook property the most. You'll see evidence of this throughout the book. The following procedure demonstrates these properties:

Sub WhatIsActive()

Debug.Print Application.ActiveCell.Address Debug.Print Application.ActivePrinter Debug.Print Application.ActiveSheet.Name Debug.Print Application.ActiveWindow.Caption Debug.Print Application.ActiveWorkbook.Name Debug.Print Application.Selection.Address Debug.Print Application.ThisWorkbook.Name End Sub

Running this procedure produces the following output (your results may vary) $A$1

HP LaserJet 4000 Series PCL6 on Ne00: Sheet3

Chapter Five Examples.xls Chapter Five Examples.xls $A$1

Chapter Five Examples.xls

Was this article helpful?

0 0

Post a comment