Microsoft Office Tutorials and References

In Depth Information

**Appendix G. New Objects in Excel XP**

Error objects determine whether or not a particular type of error has occurred in a specified range.

In particular, the Errors property of the Range object returns an Errors collection. This collection

always contains exactly seven Error objects, corresponding to the seven types of available

errorchecking options, as shown in the Error Checking tab of the Options dialog box (see the

ErrorCheckingOptions entry).

The Error object has a Boolean, read-only Value property that is set to
True
by Excel if there is

an error of the type corresponding to the Error object within the given range.

It is important to note that, in order for the Error object's Value property to function correctly, the

error-checking option must be enabled, either programmatically or through the Error Checking tab.

To illustrate, the following code first turns on global empty cell error checking. Then in cell A1, it

places a formula that references one or more empty cells. Finally, the code checks to see if there is

an empty cell reference error within the range denoted by rng.

Sub Example_ErrorObject()

Dim rng As Range

Set rng = Application.Range("A1")

' Make sure empty cell error checking is on globally

Application.ErrorCheckingOptions.EmptyCellReferences = True

' Insert formula referencing empty cells

' in a cell within the range rng

Range("A1").Formula = "=A12+A13"

' Check to see if there is an empty cell type error in the

specified range

If rng.Errors.Item(xlEmptyCellReferences).Value = True Then

MsgBox "Empty cell error in range " & rng.Address

Else

MsgBox "No empty cell error in range " & rng.Address

End If

End Sub

Note that if we change the specified range to one that contains more than one cell, as in:

Set rng = Application.Range("A1:B2")

then the line:

If rng.Errors.Item(xlEmptyCellReferences).Value = True Then

generates a generic (and therefore totally useless) error message! There is certainly no reason why

the Errors property of the Range object could not work with multi-cell ranges and the

documentation mentions no such restriction. Of course, there is no way to tell whether there is

simply an omission in the documentation or an error in the Excel code. The only clue is that the

Error object does not have any properties that returns a list of the offending cells within the range.

This might lead us to believe that the Errors property was intended to apply only to one-cell

ranges.