Using the ErrorCheckingOptions and Watch Objects to Ensure the Accuracy of Spreadsheets
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.
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 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.
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|
| ||The name of the workbook that contains the watch.|
| ||The name of the worksheet there the watched cell resides.|
| ||The name that has been defined for the cell, if any.|
| ||The address of the cell that is being watched.|
| ||The value of the cell that is being watched.|
| ||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.
For more information on the ErrorCheckingOptions and Watch objects, see Excel Visual Basic® Help.