Information
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.
This topic has not yet been rated - Rate this topic

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

 

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.

Contents

Introduction
The Watch Report Add-In

Accessing Watches Programmatically

Additional Information

Introduction

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.

Code What is returned
.Source.Parent.Parent.Name The name of the workbook that contains the watch.
.Source.Parent.Name The name of the worksheet there the watched cell resides.
.Source.Name.Name The name that has been defined for the cell, if any.
.Source.Address The address of the cell that is being watched.
.Source.Value The value of the cell that is being watched.
.Source.Formula The 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.

Show:
© 2014 Microsoft. All rights reserved.