C H A P T E R    4 Microsoft Office 97/Visual Basic Programmer's Guide

Microsoft Excel Objects

Contents

Visual Basic supports a set of objects that correspond directly to elements in Microsoft Excel, most of which you're familiar with from the user interface. For example, the Workbook object represents a workbook, the Worksheet object represents a worksheet, and the Range object represents a range of cells on a worksheet. Every element in Microsoft Excel — each workbook, worksheet, chart, cell, and so on — can be represented by an object in Visual Basic. By creating procedures that control these objects, you automate tasks in Microsoft Excel. The object model in Microsoft Excel 97 contains extensive changes and modifications. To view a graphical representation of the Microsoft Excel 97 object model, see "Microsoft Excel Objects" in Help. For a list of changes and additions, see "Changes to the Microsoft Excel 97 Object Model" in Help.

Microsoft Excel 97 adds support for event-driven programming to Visual Basic. An event is any action recognized by a Microsoft Excel object. Several objects in Microsoft Excel recognize a predefined set of events; when you want an object to respond to an event in a particular way, you can write a Visual Basic event procedure for that event.

How Do I Display Visual Basic Help for Microsoft Excel?

To use Visual Basic Help for Microsoft Excel, you must click Custom during Setup and select the Online Help for Visual Basic check box for Microsoft Excel. Otherwise, Visual Basic Help won't be installed. If you've already installed Microsoft Excel, you can run Setup again to install Visual Basic Help.

To see the contents and index of Visual Basic Help for Microsoft Excel, click Contents and Index on the Help menu in the Visual Basic Editor. On the Contents tab in the Help Topics dialog box, double-click "Microsoft Excel Visual Basic Reference," and then double-click "Shortcut to Microsoft Excel Visual Basic Reference." The Help Topics dialog box should reappear, displaying the contents and index for Visual Basic Help for Microsoft Excel.

Working with the Application Object

Most properties of the Microsoft Excel Application object control the appearance of the application window or the global behavior of the application. For example, the value of the DisplayFormulaBar property is True if the formula bar is visible, and the value of the ScreenUpdating property is False if screen updating is turned off.

In addition, properties of the Application object provide access to objects lower in the object hierarchy, such as the Windows collection (representing all currently open windows) and the Workbooks collection (representing all currently open workbooks). You use these properties, sometimes called accessors, to move down the object hierarchy from the top­level Application object to objects lower in the hierarchy (such as the Workbook, Worksheet, and Range objects). For more information about navigating through an object model, see Chapter 2, "Understanding Object Models."

Some methods and properties that apply to the Application object also apply to objects lower in the object hierarchy. Using these properties or methods at the Application level usually changes all open workbooks or sheets. For example, the Calculate method applies to the Application, Workbook, and Worksheet objects. Using Application.Calculate recalculates all worksheets in all open workbooks, whereas using this method on the Workbook or Worksheet object provides greater control.

Working with the Workbook Object

When you open or save a file in Microsoft Excel, you're actually opening and saving a workbook. In Visual Basic, the methods for manipulating files are methods of the Workbook object or the Workbooks collection.

Opening Workbooks

When you open a workbook, you use the Open method. The Open method always applies to the Workbooks collection, which you return using the Workbooks property. The following code opens the file Book1.xls (in the current folder) and then displays the value that's in cell A1 on the first worksheet in the workbook. 

Sub OpenBook1()
	Set myBook = Workbooks.Open(Filename:="BOOK1.XLS")
	MsgBox myBook.Worksheets(1).Range("A1").Value
End Sub

Notice that the return value of the Open method is a Workbook object that refers to the workbook that was just opened.

The file name in this example doesn't contain a path; therefore, the file is assumed to be in the current folder. This is guaranteed to cause a run­time error, because as soon as the user changes the current folder, Visual Basic can no longer find the file.

There are two relatively safe places to store a workbook you want to open programmatically. One place is the folder that contains the executable file for Microsoft Excel. The other place is the Library folder, which is created automatically during setup; this folder is one level down from the folder that contains the executable file.

If you want to open a workbook that's saved in the folder that contains the executable file, you can use the Path property to return a string that specifies the folder. The PathSeparator property returns the correct separator character for the current file system (for example, a backslash (\) for MS­DOS®/Windows® FAT, or a colon (:) for the Macintosh®). The following example shows file­system­independent code you can use to open Book1.xls, assuming that Book1.xls is saved in the folder that contains the executable file. 

Sub OpenBook1()
	EXEPath = Application.Path & Application.PathSeparator
	fName = EXEPath & "BOOK1.XLS"
	Set myBook = Workbooks.Open(Filename:=fName)
	MsgBox myBook.Worksheets(1).Range("A1").Value
End Sub

The other relatively safe place to store a workbook is in the Library folder. You can use the LibraryPath property instead of the Path property to return a string that specifies the Library folder. The following code shows how you would alter the preceding example to use the LibraryPath property. 

Sub OpenBook1()
	LibPath = Application.LibraryPath & Application.PathSeparator
	fName = LibPath & "BOOK1.XLS"
	Set myBook = Workbooks.Open(Filename:=fName)
	MsgBox myBook.Worksheets(1).Range("A1").Value
End Sub

Instead of hard­coding a file name with the Open method, you may want to give the user the option of selecting a file to open. The GetOpenFilename method displays the standard Open dialog box, but the method returns a string instead of opening a file. The string contains the fully qualified path and file name. The following example demonstrates the GetOpenFilename method by displaying the return value in a message box and then opening the file. 

Sub DemoGetOpenFilename()
	Do
		fName = Application.GetOpenFilename
	Loop Until fName <> False
	MsgBox "Opening " & fName
	Set myBook = Workbooks.Open(Filename:=fName)
End Sub

Creating and Saving Workbooks

You create a new workbook by applying the Add method to the Workbooks collection. Remember to set the return value of the Add method to an object variable so that you can refer to the new workbook in your code.

When you save a new workbook for the first time, use the SaveAs method. For subsequent saves, use the Save method. The GetSaveAsFilename method is very similar to the GetOpenFilename method, which is described in the preceding section. The following example shows how to create a new workbook and then save it using the GetSaveAsFilename method. 

Sub CreateAndSave()
	Set newBook = Workbooks.Add
	Do
		fName = Application.GetSaveAsFilename
	Loop Until fName <> False
	newBook.SaveAs Filename:=fName
End Sub

Closing Workbooks

To close a workbook, use the Close method of the Workbook object. You can close a workbook without saving changes, as shown in the following example. 

Sub OpenChangeClose()
	Do
		fName = Application.GetOpenFilename
	Loop Until fName <> False
	Set myBook = Workbooks.Open(Filename:=fName)
	' Make some changes to myBook
	myBook.Close savechanges:=False
End Sub

This code uses the GetOpenFilename method to select the workbook to open, makes some changes to the workbook (indicated by the comments), and then closes the workbook without saving the changes.

Working with the Range Object

The Range object can represent a single cell, a range of cells, an entire row or column, a selection containing multiple areas, or a 3­D range. The Range object is somewhat unusual in that it can represent both a single cell and multiple cells. There's no separate collection object for the Range object; you can think of it as being either a single object or a collection, depending on the situation. There are many different properties and methods that return a Range object, as shown in the following list.

ActiveCell

BottomRightCell

Cells

ChangingCells

CircularReference

Columns

CurrentArray

CurrentRegion

Dependents

DirectDependents

DirectPrecedents

EntireColumn

EntireRow

Next

Offset

PageRange

Precedents

Range

RowFields

RowRange

Rows

Selection

TableRange1

TableRange2

TopLeftCell

UsedRange

VisibleRange

For more information about these properties and methods, see the individual property and method topics in Help.

Using an A1­Style String Reference or Range Name

One of the most common ways to return a Range object is to use an A1­style reference or the name of a range, as shown in the following table.

To do this Use the following code
Set the value of cell A1 on Sheet1 Worksheets("Sheet1").Range("A1").Value = 3
Set the formula for cell B1 on the active sheet Range("B1").Formula = "=5-10*RAND()"
Set the value of each cell in the range C1:E3 on the active sheet Range("C1:E3").Value = 6
Clear the contents of the range A1:E3 on the active sheet Range("A1", "E3").ClearContents
Set the font style for the range named "myRange" (a workbook-level name) to bold Range("myRange").Font.Bold = True
Set the value of each cell in the range named "yourRange" (a sheet-level name) Range("Sheet1!yourRange").Value = 3
Set an object variable to refer to a range Set objRange = Range("myRange")

Remember that expressions such as Range("C1:E3").Value = 6 assume that the Range property operates on the active sheet. If you try to run this code with a chart sheet active, a run­time error occurs (error 1004, "Range method of Application class failed").

Another cause of errors is the use of the Range property in an argument to another method, without fully qualifying the Worksheet object to which the Range property applies. The following example, which is supposed to sort a range of cells on Sheet1, also causes run­time error 1004. 

Sub SortRange()
Worksheets("Sheet1").Range("A1:B10").Sort _
	key1:=Range("A1"), order1:=xlDescending
End Sub

This error is more difficult to find, because the line that contains the Sort method is correct. The error is caused by the second line, which contains the Key1 argument. This code will run correctly if Sheet1 is the active sheet, but it will fail when it's run from another worksheet or from a module. To avoid the error, use the Worksheets property in the argument. 

Sub SortRange()
	Worksheets("Sheet1").Range("A1:B10").Sort _
		key1:=Worksheets("Sheet1").Range("A1"), order1:=xlDescending
End Sub

Using Numeric Row and Column Indexes

You can also return a specific cell by specifying its row and column numbers, or indexes. You specify the row index first, followed by the column index, as shown in the following table.

To do this Use the following code
Set the value of cell A1 on Sheet1 Worksheets("Sheet1").Cells(1, 1).Value = 3
Set the formula for cell B1 on the active sheet Cells(1, 2).Formula = "=5-10*RAND()"
Set an object variable Set objRange = Worksheets("Sheet1").Cells(1, 1)

Numeric row and column indexes are useful when you want to refer to cells by using loop counters. For example, the following code loops through cells A1:D10 on Sheet1. If any of the cells has a value less than 0.01, the example replaces the value with 0 (zero). 

Sub RoundToZero()
	For rwIndex = 1 to 4
		For colIndex = 1 to 10
			If Worksheets("Sheet1").Cells(rwIndex, colIndex) < .01 Then
				Worksheets("Sheet1").Cells(rwIndex, colIndex).Value = 0
			End If
		Next colIndex
	Next rwIndex
End Sub

The following example shows a quick and easy way to display items in a multiple­column list. The code creates a new worksheet and sets the object variable newSheet to refer to the worksheet. The code then creates a list of all the names in the active workbook and displays their formulas in A1­style notation. 

Sub ListNames()
	Set newSheet = Worksheets.Add
	i = 1
	For Each nm In ActiveWorkbook.Names
		newSheet.Cells(i, 1).Value = nm.Name
		newSheet.Cells(i, 2).Value = "'" & nm.RefersTo
		i = i + 1
	Next nm
	newSheet.Columns("A:B").AutoFit
End Sub

Using the Offset Property

You often need to return a range of cells that's a certain number of rows or columns away from another range of cells. The Offset property applies to a Range object, takes a RowOffset argument and a ColumnOffset argument, and returns a new range. The following example determines the type of data in each cell in the range A1:A10. The code writes the data types in the column to the right of the input cells. 

Sub ScanColumn()
	For Each c In Worksheets("Sheet1").Range("A1:A10").Cells
		If Application.IsText(c.Value) Then
			c.Offset(0, 1).Formula = "Text"
		ElseIf Application.IsNumber(c.Value) Then
			c.Offset(0, 1).Formula = "Number"
		ElseIf Application.IsLogical(c.Value) Then
			c.Offset(0, 1).Formula = "Boolean"
		ElseIf Application.IsError(c.Value) Then
			c.Offset(0, 1).Formula = "Error"
		ElseIf c.Value = "" Then
			c.Offset(0, 1).Formula = "(blank cell)"
		End If
	Next c
End Sub

Using the CurrentRegion and UsedRange Properties

These two properties are very useful when your code operates on ranges whose size you have no control over. The current region is a range of cells bounded by empty rows and empty columns, or by a combination of empty rows, empty columns, and the edges of the worksheet.

The CurrentRegion property applies to a Range object. There can be many different current regions on a worksheet, depending on the Range object to which you apply the CurrentRegion property.

Suppose that Sheet1 contains a list to which you want to apply a number format. The only thing you know about the list is that it begins at cell A1; you don't know how many rows or columns it contains. The following example shows how to format the list by using the CurrentRegion property. 

Sub FormatRange()
	Set myRange = Worksheets("Sheet1").Range("A1").CurrentRegion
	myRange.NumberFormat = "0.0"
End Sub

The used range is bounded by the farthest upper­left and farthest lower­right nonempty cells on a worksheet. It's a range that contains every nonempty cell on the worksheet, as well as all the empty cells that are interspersed among them. There can be only one used range on a worksheet; the UsedRange property applies to a Worksheet object, not to a Range object.

Suppose that the active worksheet contains data from a timed experiment. The used range contains the dates in the first column, the times in the second column, and the measurements in the third and fourth columns. You want to write code that combines each separate date and time into a single value, converts that value from Greenwich Mean Time (GMT) to Pacific Standard Time (PST), and then applies a date format to the value. The data table can contain empty rows and columns. You can use the UsedRange property to return the entire used range, including any embedded blank rows. The following example shows one way to convert and format the dates and times. 

Sub ConvertDates()
	Set myRange = ActiveSheet.UsedRange
	myRange.Columns("C").Insert
	Set dateCol = myRange.Columns("C")
	For Each c In dateCol.Cells
		If c.Offset(0, -1).Value <> "" Then
			c.FormulaR1C1 = "=RC[-2]+RC[-1]-(8/24)"
		End If
	Next c
	dateCol.NumberFormat = "mmm-dd-yyyy hh:mm"
	dateCol.Copy
	dateCol.PasteSpecial Paste:=xlValues
	myRange.Columns("A:B").Delete
	dateCol.AutoFit
End Sub

Notice that the code uses the expression ActiveSheet.UsedRange.Columns("C") to return the third column from the used range (although this is the third column in the used range, it can appear in any column on the worksheet — that is, the used range can be preceded by empty columns). You can use other Range object properties and methods in a similar way to build complex expressions that return subranges or super­ranges of a Range object. Some properties and methods commonly used in this way are Areas, Cells, Columns, EntireColumn, EntireRow, Range, and Rows.

Looping on a Range of Cells

There are several different ways to loop on the cells in a range. The examples in this section show the For Each...Next statement and the Do...Loop statement applied to looping on a range of cells.

Using For Each...Next

The recommended way to loop on the cells in a range is to use the For Each...Next loop, which is also the recommended way to loop on the elements in a collection.

The following example shows how to loop through the range A1:D10 on Sheet1, setting any number whose absolute value is less than 0.01 to 0 (zero). 

Sub RoundToZero()
	For Each r In Worksheets("Sheet1").Range("A1:D10").Cells
		If Abs(r.Value) < 0.01 Then
			r.Value = 0
		End If
	Next r
End Sub

Suppose that you want to modify this code to loop over a range of cells that a user selects. One way of doing this is to use the InputBox method to prompt the user to select a range of cells. The InputBox method returns a Range object that represents the selection. By using the Type argument and error handling, you can ensure that the user selects a valid range of cells before the input box is dismissed. 

Sub RoundToZero()
	Worksheets("Sheet1").Activate
	On Error GoTo PressedCancel
	Set r = Application.InputBox( _
			prompt:="Select a range of cells", _
			Type:=8)
	On Error GoTo 0
	For Each c In r.Cells
		If Abs(c.Value) < 0.01 Then
			c.Value = 0
		End If
	Next c
	Exit Sub

PressedCancel:
	Resume
End Sub

If you don't want the user to select the range, you may be able to use the CurrentRegion property or the UsedRange property to return a Range object. For example, if you know that the data on Sheet1 begins at cell A1 and includes no empty rows or columns, you can use the CurrentRegion property to return the entire range automatically. 

Sub RoundToZero()
	Set r = Worksheets("Sheet1").Range("A1").CurrentRegion
	For Each c In r.Cells
		If Abs(c.Value) < 0.01 Then
			c.Value = 0
		End If
	Next c
End Sub

The following two examples show two different ways to hide every other column in the used range on Sheet1. The first example shows a For Each...Next loop in which the Column property of the object variable is tested. 

Sub HideColumns()
	Set r = Worksheets("Sheet1").UsedRange
	For Each col In r.Columns
		If col.Column Mod 2 = 0 Then
			col.Hidden = True
		End If
	Next col
End Sub

The second example shows a For...Next loop that tests the loop counter. 

Sub HideColumns()
	Set r = Worksheets("Sheet1").UsedRange
	For i = 1 To r.Columns.Count
		If i Mod 2 = 0 Then
			r.Columns(i).Hidden = True
		End If
	Next i
End Sub

Using Do...Loop

Occasionally, the For Each...Next loop isn't the best way to loop on a range. Suppose that you have a column of data and you want to write a macro that sorts the data and then deletes rows that contain duplicate data. You could try to use a For Each...Next loop, as shown in the following example. 

Sub BuggyRemoveDuplicates()    ' DON'T USE THIS CODE!
	Worksheets("Sheet1").Range("A1").Sort _
		key1:=Worksheets("Sheet1").Range("A1")
	Set r = Worksheets("Sheet1").Range("A1").CurrentRegion.Columns("A")
	For Each c In r.Cells
		If c.Offset(1, 0).Value = c.Value Then
			c.Offset(1, 0).EntireRow.Delete
		End If
	Next c
End Sub

Unfortunately, this code doesn't work correctly because the Delete method is modifying the range on which For Each...Next is looping. This causes duplicates not to be deleted in some cases.

A better solution is to use a Do...Loop structure, as shown in the following example. 

Sub GoodRemoveDuplicates()
	Worksheets("Sheet1").Range("A1").Sort _
			key1:=Worksheets("Sheet1").Range("A1")
	Set currentCell = Worksheets("Sheet1").Range("A1")
	Do While Not IsEmpty(currentCell)
		Set nextCell = currentCell.Offset(1, 0)
		If nextCell.Value = currentCell.Value Then
			currentCell.EntireRow.Delete
		End If
		Set currentCell = nextCell
	Loop
End Sub

The loop tests the object variable currentCell, exiting when it encounters an empty cell at the bottom of the column of data. You could build an equivalent loop by testing the value in currentCell against an empty string, as shown in the following example. 

	Do While currentCell.Value <> ""
		' Code to run on cells with values
	Loop

In either case, don't forget to increment the cell at the bottom of the Do...Loop structure (Set currentCell = nextCell, for example).

Using the Address Property to Debug Range Object Code

You can apply the Address property to any Range object. The Address property returns the cell address of a range, as a string. The following example shows how to use the Address property to debug the HideColumns procedure. 

Sub HideColumns()
	Set r = Worksheets("Sheet1").UsedRange
	MsgBox r.Address  ' debugging only!
	For i = 1 To r.Columns.Count
		If i Mod 2 = 0 Then
			r.Columns(i).Hidden = True
			MsgBox r.Columns(i).Address  ' debugging only!
		End If
	Next i
End Sub

You can also set watch expressions instead of using message boxes. For the preceding example, you could set two watch expressions — r.Address and r.Columns(i).Address — and then examine the values of the watch expressions in the Immediate window. For more information about debugging, see Chapter 14, "Debugging and Error Handling."

Working with Events

If you've used Visual Basic (Standard, Professional, or Enterprise Edition), you're familiar with event­driven programming; most of your Visual Basic code was probably written to respond to events, such as when the user clicks a button or when a form is loaded. In Microsoft Excel, you may have used properties such as OnSheetActivate or OnEntry to cause a macro to run when a sheet is activated or changed. This is also event­driven programming. Microsoft Excel 97 expands the available list of events and adds event procedures that receive arguments.

With Microsoft Excel 97, you can write event procedures at the worksheet, chart, workbook, or application level. For example, the Activate event occurs at the sheet level, and the SheetActivate event is available at both the workbook and application levels. The SheetActivate event for a workbook occurs when any sheet in that workbook is activated. At the application level, the SheetActivate event occurs when any sheet in any open workbook is activated.

Worksheet and workbook event procedures are created by default for any open worksheet, chart sheet, or workbook. To write event procedures for an embedded chart or for the application, you must create a new object using the WithEvents keyword in a class module. You can also use a class module to create event procedures that can be used for more than one worksheet or workbook. For more information , see "Using Class Modules with Events" later in this chapter.

Enabling or Disabling Events

Use the EnableEvents property to enable or disable events. For example, using the Save method to save a workbook causes the BeforeSave event to occur. You can prevent this by setting the EnableEvents property to False before you call the Save method, as in the following example. 

Application. = False
ActiveWorkbook.Save
Application. = True

Using Events on Sheets

Events on sheets are enabled by default. To view the event procedures for a particular sheet, use either of the following techniques:

  • Right­click the sheet tab, and then click View Code on the shortcut menu. In the Procedure box, click the event name.

  • On the Tools menu, point to Macro and then click Visual Basic Editor. Select the sheet in the Project Explorer, and then either click the View Code button or click Code on the View menu. In the Object box, click either Worksheet or Chart, and then click the event name in the Procedure box.

Worksheet Events

Worksheet­level events occur when the user activates a worksheet or changes a worksheet cell, as shown in the following table.

EventDescription
ActivateOccurs when the user activates the sheet. Use this event instead of the OnSheetActivate property.
BeforeDoubleClickOccurs when the user double-clicks a worksheet cell. Use this event instead of the OnDoubleClick property.
BeforeRightClickOccurs when the user right-clicks a worksheet cell.
CalculateOccurs when the user recalculates the worksheet. Use this event instead of the OnCalculate property.
ChangeOccurs when the user changes a cell formula. Use this event instead of the OnEntry property.
DeactivateOccurs when the sheet is active and the user activates a different sheet. Doesn't occur when the user shifts the focus from one window to another window showing the same sheet. Use this event instead of the OnSheetDeactivate property.
SelectionChangeOccurs when the user selects a worksheet cell.

For more information about an event, see the corresponding Help topic.

Examples

The following example adjusts the size of columns A through F whenever the worksheet is recalculated. 

Private Sub Worksheet_Calculate()
	Columns("A:F").AutoFit
End Sub

Some events can be used to substitute an action for the default application behavior, or to make a small change to the default behavior. The following example traps the right­click event and adds a new menu item to the shortcut menu for cells B1:B10. 

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, _
        Cancel As Boolean)
    For Each icbc In Application.CommandBars("cell").Controls
        If icbc.Tag = "brccm" Then icbc.Delete
    Next icbc
    If Not Application.Intersect(Target, Range("b1:b10")) Is Nothing Then
            With Application.CommandBars("cell").Controls _
                .Add(Type:=msoControlButton, before:=6, _
                temporary:=True)
           .Caption = "New Context Menu Item"
           .OnAction = "MyMacro"
           .Tag = "brccm"
        End With
    End If
End Sub

Chart Events

Like worksheet­level events, chart­level events occur when the user activates or changes a chart, as shown in the following table.

EventDescription
ActivateOccurs when the user activates the chart sheet (doesn't work with embedded charts). Use this event instead of the OnSheetActivate property.
BeforeDoubleClickOccurs when the user double-clicks the chart. Use this event instead of the OnDoubleClick property.
BeforeRightClickOccurs when the user right-clicks the chart.
CalculateOccurs when the user plots new or changed data on the chart.
DeactivateOccurs when the sheet is active and the user activates a different sheet. Doesn't occur when the user shifts the focus from one window to another window showing the same sheet. Use this event instead of the OnSheetDeactivate property.
DragOverOccurs when the user drags data over the chart.
DragPlotOccurs when the user drags a range of cells over the chart.
MouseDownOccurs when the user clicks a mouse button while the pointer is positioned over the chart.
MouseMoveOccurs when the user moves the pointer over the chart.
MouseUpOccurs when the user releases a mouse button while the pointer is positioned over the chart.
ResizeOccurs when the user changes the size of the chart.
SelectOccurs when the user selects a chart element.
SeriesChangeOccurs when the user changes the value of a chart data point.

For more information about an event, see the corresponding Help topic.

Events for chart sheets are available by default in the Visual Basic Editor. To write event procedures for an embedded chart, you must create a new object using the WithEvents keyword in a class module. For more information, see "Using Class Modules with Events" later in this chapter.

Example

The following example changes a point's border color when the user changes the point's value. 

Private Sub Chart_SeriesChange(ByVal SeriesIndex As Long, _
		ByVal PointIndex As Long)
    Set p = ActiveChart.SeriesCollection(SeriesIndex).Points(PointIndex)
    p.Border.ColorIndex = 3
End Sub

Workbook Events

Workbook events occur when the user changes a workbook or any sheet in the workbook.

EventDescription
ActivateOccurs when the user activates the workbook.
AddInInstallOccurs when the user installs the workbook as an add­in. Use this event instead of the Auto_Add macro.
AddInUninstallOccurs when the user uninstalls the workbook as an add-in. Use this event instead of the Auto_Remove macro.
BeforeCloseOccurs before the workbook closes. Use this event instead of the Auto_Close macro.
BeforePrintOccurs before the workbook is printed.
BeforeSaveOccurs before the workbook is saved. Use this event instead of the OnSave property.
DeactivateOccurs when the workbook is active and the user activates a different workbook.
NewSheetOccurs after the user creates a new sheet.
OpenOccurs when the user opens the workbook. Use this event instead of the Auto_Open macro.
SheetActivateOccurs when the user activates a sheet in the workbook. Use this event instead of the OnSheetActivate property.
SheetBeforeDoubleClick Occurs when the user double-clicks a worksheet cell (not used with chart sheets). Use this event instead of the OnDoubleClick property.
SheetBeforeRightClick Occurs when the user right-clicks a cell on a worksheet (not used with chart sheets).
SheetCalculateOccurs after the user recalculates a worksheet (not used with chart sheets). Use this event instead of the OnCalculate property.
SheetChangeOccurs when the user changes a cell formula (not used with chart sheets). Use this event instead of the OnEntry property.
SheetDeactivateOccurs when the user activates a different sheet in the workbook. Use this event instead of the OnSheetDeactivate property.
SheetSelectionChange Occurs when the user changes the selection on a worksheet (not used with chart sheets).
WindowActivateOccurs when the user shifts the focus to any window showing the workbook. Use this event instead of the OnWindow property.
WindowDeactivateOccurs when the user shifts the focus away from any window showing the workbook. Use this event instead of the OnWindow property.
WindowResizeOccurs when the user opens, resizes, maximizes, or minimizes any window showing the workbook.

For more information about an event, see the corresponding Help topic.

Example

The following example maximizes the Microsoft Excel application window when the workbook is opened. 

Sub Workbook_Open()
	Application.WindowState = xlMaximized
End Sub

Application Events

Application events occur when the user creates or opens a workbook or when the user changes any sheet in any open workbook.

EventDescription
NewWorkbookOccurs when the user creates a new workbook.
SheetActivateOccurs when the user activates a sheet in an open workbook. Use this event instead of the OnSheetActivate property.
SheetBeforeDoubleClick Occurs when the user double-clicks a worksheet cell in an open workbook (not used with chart sheets). Use this event instead of the OnDoubleClick property.
SheetBeforeRightClick Occurs when the user right-clicks a worksheet cell in an open workbook (not used with chart sheets).
SheetCalculateOccurs after the user recalculates a worksheet in an open workbook (not used with chart sheets). Use this event instead of the OnCalculate property.
SheetChangeOccurs when the user changes a cell formula in an open workbook (not used with chart sheets). Use this event instead of the OnEntry property.
SheetDeactivateOccurs when the user deactivates a sheet in an open workbook. Use this event instead of the OnSheetDeactivate property.
SheetSelectionChange Occurs when the user changes the selection on a sheet in an open workbook.
WindowActivateOccurs when the user shifts the focus to an open window. Use this event instead of the OnWindow property.
WindowDeactivateOccurs when the user shifts the focus away from an open window. Use this event instead of the OnWindow property.
WindowResizeOccurs when the user resizes an open window.
WorkbookActivateOccurs when the user shifts the focus to an open workbook.
WorkbookAddInInstall Occurs when the user installs a workbook as an add-in.
WorkbookAddInUninstall Occurs when the user uninstalls a workbook as an add-in.
WorkbookBeforeClose Occurs before an open workbook is closed.
WorkbookBeforePrint Occurs before an open workbook is printed.
WorkbookBeforeSave Occurs before an open workbook is saved.
WorkbookDeactivate Occurs when the user shifts the focus away from an open workbook.
WorkbookNewSheetOccurs when the user adds a new sheet to an open workbook.
WorkbookOpenOccurs when the user opens a workbook.

For more information about an event, see the corresponding Help topic.

Using Class Modules with Events

Unlike sheet events, embedded charts and the Application object don't have events enabled by default. Before you can use events with an embedded chart or with the Application object, you must create a new class module and declare an object of type Chart or Application with events. You use the Class Module command (Insert menu) in the Visual Basic Editor to create a new class module.

To enable the events of the Application object, you'd add the following declaration to the class module. 

Public WithEvents App As Application

After the new object has been declared with events, it appears in the Object box in the class module, and you can write event procedures for the new object. (When you select the new object in the Object box, the valid events for that object are listed in the Procedure box.)

Before the procedures will run, however, you must connect the declared object in the class module to the Application object. You can do this from any module by using the following declaration (where "EventClass" is the name of the class module you created to enable events). 

Public X As New EventClass

After you've created the X object variable (an instance of the EventClass class), you can set the App object of the EventClass class equal to the Microsoft Excel Application object. 

Sub InitializeApp()
	Set X.App = Application
End Sub

After you run the InitializeApp procedure, the App object in the EventClass class module points to the Microsoft Excel Application object, and the event procedures in the class module will run whenever the events occur.

Although this may seem like a lot of work, one advantage is that you can use the same event procedure for many objects. For example, suppose that you declare an object of type Chart with events in a class module, as follows. 

Public WithEvents cht As Chart

You can then use the following code to cause the event procedures to run whenever an event occurs for either chart one or chart two. 

Dim C1 As New EventClass
Dim C2 As New EventClass

Sub InitializeCharts
	Set C1.cht = Worksheets(1).ChartObjects(1).Chart
	Set C2.cht = Worksheets(1).ChartObjects(2).Chart
End Sub

You can declare Worksheet or Workbook objects with events in a class module and use the events in the new class with several sheets, in addition to the default event procedures. You might use this technique to write an Activate event handler that runs only when either sheet one or sheet five is activated. Or you can use a Chart object declared in a class module to write an event handler for both embedded charts and chart sheets.