Page 53 - Microsoft Office Excel 2003 Programming Inside Out

Exploring Visual Basic for Applications

property. Changing the name of a worksheet when you’ve edited or updated the values in one
or more worksheet cells lets you and your colleagues know that the data on that sheet is new
and should be checked before the worksheet is included in any final written products.

Methods

A method is an action that an object “knows” how to perform. For example, you probably
know that the worksheet displayed in the Excel window is referred to as the active worksheet.
In Excel VBA, you can change the worksheet you’re affecting with your VBA code by calling
the target worksheet’s Activate method. After the Activate method runs, the worksheet to which
it is attached moves to the front of the worksheets in the Excel window and becomes available
for editing. As with properties, methods are called using dot notation. To recalculate all of the
formulas in a worksheet, for example, you would call the Worksheet.Calculate method.

Events

Just as a property is a quantifiable attribute of an object and a method is an action an object
knows how to take, an event is an action an object recognizes as having happened. For exam€
ple, Excel 2003 knows about the following events (among many others): Chapter 3
● A workbook is opened or closed.
● A worksheet is activated or deactivated.
● A workbook is saved.
● A chart is clicked.
● A key (or combination of keys) is pressed.
● Data is typed into a cell.
● The formulas in a worksheet are recalculated.
● A hyperlink is followed.

Excel comes with a number of event handlers, or code routines that watch for particular
actions to occur. When one of those actions does occur, and you’ve told Excel what you want
it to do when the event happens, Excel will run the code in your event handler. For example,
if after creating a new workbook you want Excel to display all open workbooks as a cascaded
set of windows, you could create the following event handler:
Private Sub App_NewWorkbook(ByVal Wb As Workbook)
Application.Windows.Arrange xlArrangeStyleCascade
End Sub
Don’t worry if you’re not sure what each and every element of the event handler routine does;
for now you can concentrate on the middle line of code, which tells the Excel application to
arrange its windows using the cascade style. It’s the same result that would occur if you
clicked Window, Arrange, Cascade in the Excel menu system, but if it’s an action you want to

27
Part 2: Visual Basic for Applications