The topic you requested is included in another documentation set. For convenience, it's displayed below. Choose Switch to see the topic in its original location.

Using the ErrorCheckingOptions and Watch Objects to Ensure the Accuracy of Spreadsheets

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

Michael Stowe
Microsoft Corporation

April 2001

Applies to:
   Microsoft® Excel 2002

Summary: This article includes an add-in that you can use to enhance the Watch Window toolbar. (2 printed pages)

Download odc_webErrChek.exe.


Introduction The Watch Report Add-In Accessing Watches Programmatically
Additional Information


Microsoft® Excel 2002 introduces a new feature that allows you to track the value of key cells in your worksheets. This is done through the Watch Window toolbar.

Unfortunately, the toolbar can be difficult to navigate if you have added a lot of watches to your open workbooks. There is no facility to filter the watches based upon the workbook that they apply to. Also, you can't print the information displayed in the Watch Window toolbar.

This article includes a downloadable add-in solution that allows you to address these limitations.

The Watch Report Add-In

The Watch Report add-in adds a command titled List Watches in New Worksheet to the Formula Auditing submenu (Tools menu). If any open workbooks contain watches, clicking this command creates a new workbook and then writes the information displayed in the Watch Windows toolbar to the new workbook. You can sort, print, and filter this report like any other Excel workbook. The report is a snapshot of the current watches; it does not update when any workbooks that contain watches recalculate.

Accessing Watches Programmatically

The information displayed in the report is obtained by iterating through the Application object's Watches collection. The Source property of each Watch object returns a Range object representing the cell that is being watched. The information in the report is derived from the Range returned by the Source property. The following table lists how the information contained in the report was derived from the Watch object.

CodeWhat is returned
.Source.Parent.Parent.NameThe name of the workbook that contains the watch.
.Source.Parent.NameThe name of the worksheet there the watched cell resides.
.Source.Name.NameThe name that has been defined for the cell, if any.
.Source.AddressThe address of the cell that is being watched.
.Source.ValueThe value of the cell that is being watched.
.Source.FormulaThe formula of the cell that is being watched.

When iterating the Watches collection you must use the For...Next keywords instead of the For Each...Next keywords. Also, the Watches collection is zero-based.

The ReportCode.bas file in the sample download contains the code in the WatchReport.xla add-in that creates the report.

The ThisWorkbook.cls file in the sample download contains the code in the WatchReport.xla add-in that handles adding and deleting the menu item for the add-in.

Additional Information

For more information on the ErrorCheckingOptions and Watch objects, see Excel Visual Basic® Help.

© 2014 Microsoft