Error Checking Tab


Settings

Application.ErrorCheckingOptions.BackgroundChecking = True 
Application.ErrorCheckingOptions.IndicatorColorIndex = 1

BackgroundChecking

Creates a "divide by zero" error and displays the Error Checking Options smart tag.

Sub CheckBackground() 
    Application.ErrorCheckingOptions.BackgroundChecking = True
    Range("A1").Select
    ActiveCell.Formula = "=A2/A3"
End Sub

IndicatorColorIndex

Returns or sets the color of the indicator for error checking options. Read/write XlColorIndex.
You can specify a particular color for the indicator by entering the corresponding index value. You can use the Colors property to return the current color palette.
Checks to see if the indicator color for error checking is set to the default system color and notifies the user accordingly.

Sub CheckIndexColor() 
    If Application.ErrorCheckingOptions.IndicatorColorIndex = xlColorIndexAutomatic Then
        MsgBox "Your indicator color for error checking is set to the default system color."
    Else
        MsgBox "Your indicator color for error checking is not set to the default system color."
    End If
End Sub

Rules

Application.ErrorCheckingOptions.EvaluateToError = True 
Application.ErrorCheckingOptions.TextDate = True
Application.ErrorCheckingOptions.NumberAsText = True
Application.ErrorCheckingOptions.InconsistentFormula = True
Application.ErrorCheckingOptions.OmittedCells = True
Application.ErrorCheckingOptions.UnlockedFormulaCells = True
Application.ErrorCheckingOptions.EmptyCellReferences = True

EvaluateToError

Creates a "divide by zero" error and displays the Error Checking Options smart tag.

Sub CheckEvaluationError() 
    Application.ErrorCheckingOptions.EvaluateToError = True
    Range("A1").Value = 1
    Range("A2").Value = 0
    Range("A3").Formula = "=A1/A2"
End Sub

TextDate

THIS DOES NOT WORK !!
Enters a reference to a text date with a two-digit year and displays the Error Checking Options smart tag.

Sub CheckTextDate() 
    Application.ErrorCheckingOptions.TextDate = True
    Range("B2").Value = "'April 23, 00"
End Sub

Perform check to see if 2 digit year TextDate check is on.

Sub DisplayOptionSettings() 
    Dim rngFormula As Range
    Set rngFormula = Application.Range("A1")
    Range("A1").Formula = "'April 23, 00"
    Application.ErrorCheckingOptions.TextDate = True
    If rngFormula.Errors.Item(xlTextDate).Value = True Then
        MsgBox "The text date error checking feature is enabled."
    Else
        MsgBox "The text date error checking feature is not on."
    End If
End Sub

NumberAsText

Enters a reference to a number stored as text and displays the Error Checking Options smart tag.
Sub CheckNumberAsText()
Application.ErrorCheckingOptions.NumberAsText = True
Range("A1").Value = "'1"
End Sub


InconsistentFormula

Enters an inconsistent formula and displays the Error Checking Options smart tag.

Sub CheckInconsistentFormula() 
    Application.ErrorCheckingOptions.InconsistentFormula = True
    Range("A1:A3").Value = 1
    Range("B1:B3").Value = 2
    Range("C1:C3").Value = 3
    Range("A4").Formula = "=SUM(A1:A3)" ' Consistent formula.
    Range("B4").Formula = "=SUM(B1:B2)" ' Inconsistent formula.
    Range("C4").Formula = "=SUM(C1:C3)" ' Consistent formula.
End Sub

Consistent formulas in the region must reside to the left and right or above and below the cell containing the inconsistent formula for the InconsistentFormula property to work properly.


OmittedCells

Enters a formula that refers to a range that omits adjacent cells that could be included and displays the Error Checking Options smart tag.

Sub CheckOmittedCells() 
    Application.ErrorCheckingOptions.OmittedCells = True
    Range("A1").Value = 1
    Range("A2").Value = 2
    Range("A3").Value = 3
    Range("A4").Formula = "=Sum(A1:A2)"
End Sub

UnLockedFormulaCells

Enters a formula in a cell that has been unlocked and displays the Error Checking Options smart tag.

Sub CheckUnlockedCell() 
    Application.ErrorCheckingOptions.UnlockedFormulaCells = True
    Range("A1").Value = 1
    Range("A2").Value = 2
    Range("A3").Formula = "=A1+A2"
    Range("A3").Locked = False
End Sub

EmptyCellReferences

Enters a formula that refers to empty cells and displays the Error Checking Options smart tag.

Sub CheckEmptyCells() 
    Application.ErrorCheckingOptions.EmptyCellReferences = True
    Range("A1").Formula = "=A2+A3"
End Sub

© 2019 Better Solutions Limited. All Rights Reserved. © 2019 Better Solutions Limited TopPrevNext