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.
Search JabSto ::




Custom Search