Chapter 42: Introducing Visual Basic for Applications
42
Contrary to what you may think, Excel doesn’t have a Cell object. When you want to manipulate a single cell, you
use the Range object (with only one cell in it).
The Application object has several useful properties that refer to where the user is in
the program:Application.ActiveWorkbook: Returns the active workbook (a Workbook object)
in Excel.
Application.ActiveSheet: Returns the active sheet (a Sheet object) of the active
workbook.
Application.ActiveCell: Returns the active cell (a Range object) of the active
w indow.
Application.Selection: Returns the object that is currently selected in the active
window of the Application object. This can be a Range, a Chart, a Shape, or
some other selectable object.In many cases, you can refer to the same object in a number of different ways. Assume you
have a workbook named Sales.xlsx and that it’s the only workbook open. Furthermore,
assume that this workbook has one worksheet, named Summary. Your VBA code can refer to
the Summary sheet in any of the following ways:
Workbooks("Sales.xlsx").Worksheets("Summary")Workbooks(1).Worksheets(1)Workbooks(1).Sheets(1)Application.ActiveWorkbook.ActiveSheetActiveWorkbook.ActiveSheetActiveSheetThe method that you use is determined by how much you know about the workspace. For
example, if more than one workbook is open, the second or third method isn’t reliable. If
you want to work with the active sheet (whatever it may be), any of the last three methods
would work. To be absolutely sure you’re referring to a specific sheet on a specific work-
book, the first method is your best choice.Methods
Objects also have methods. You can think of a method as an action taken with an object.
Generally, methods are used to interact with the computer outside the Excel application or
to modify multiple properties at once. For example, Range objects have a Clear method.