Excel Object Model: Errors

Item(…)

….errors.item(ix).value returns true if the error that is identified by the index ix is present. ix is a value of the xlErrorChecks enumeration:

`xlEmptyCellReferences`	7	The cell contains a formula referring to empty cells.
`xlEvaluateToError`	1	The cell evaluates to an error value.
`xlInconsistentFormula`	4	The cell contains an inconsistent formula for a region.
`xlInconsistentListFormula`	9	The cell contains an inconsistent formula for a list.
`xlListDataValidation`	8	The cell contains a value inconsistent with list data validation.
`xlMisleadingFormat`	10	(This member is not documented on the respective Microsoft documentation page).
`xlNumberAsText`	3	The cell contains a number stored as text.
`xlOmittedCells`	5	The cell contains a formula omitting a cell for a region.
`xlTextDate`	2	The cell contains a text date with 2-digit years.
`xlUnlockedFormulaCells`	6	The cell, which is unlocked, contains a formula.

? activeCell.errors.item(xlTextDate).value
False

See also the section Error checking rules under the Menu File -> Option -> Formulas.

No Count property / no iteration possible

Note that the Errors object, unlike many other «plural» objects, does not have a .count property and that it cannot be iterated over with a for each statement.

Example

The following example creates a cell with a Misleading format error and tries to detect it with Visual Basic for Applications:

option explicit

sub main() ' {

    dim ws as worksheet
    
    set ws = activeWorkbook.worksheets.add
    
    ws.cells(1, 1).value        =  43870.51
    ws.cells(1, 2).formulaR1C1  = "= RC[-1]"
    ws.cells(2, 1).value        =  43970.920347
    ws.cells(2, 2).value        = #2020-05-19 22:05:18#

    ws.cells(1, 2).numberFormat = "yyyy-mm-dd hh:mm:ss"


    checkForMisleadingFormat ws

end sub ' }

sub checkForMisleadingFormat(ws as worksheet) ' {

     dim c as range
     for each c in ws.usedRange ' {

         if c.errors(xlMisleadingFormat).value then ' {

            debug.print("Misleading format error found in " & c.address)

         end if' }

     next c ' }

end sub ' }

Github repository about-MS-Office-object-model, path: /Excel/Errors/xlMisleadingFormat.bas

When run, the debug.output statement prints

Misleading format error found in $B$1

Ignoring errors in the immediate window

I find it easier to ignore errors with the immediate window instead of using the mouse:

activeCell.errors(xlInconsistentFormula).ignore = true

It's also possible to first select the cells where the errors should be ignored and then to iterate over them:

for each for each c in selection: c.errors(xlNumberAsText).ignore = true: next c