Excel VBA WorksheetFunction.IsError false positive

Question

In VBA, I am trying to determine if a cell contains an error value, for example. due to invalid function. My existing code uses the Excel.WorksheetFunction.IsError method , but I recently came across a case that caused a false positive. The cell does not contain an error value, but Excel.WorksheetFunction.IsError returns true. Another method, VBA.Information.IsError, does not exhibit this behavior; it returns false correctly.

I was able to determine that the problem only occurs when the cell contains a value greater than 255 characters. Here is the behavior verification procedure:

Sub IsErrorBehavior()
    Dim i As Long
    Dim str As String
    Dim r1 As Range, r2 As Range
    Dim xlWSFuncCheck1 As Boolean, xlWSFuncCheck2 As Boolean
    Dim vbaInfCheck1 As Boolean, vbaInfCheck2 As Boolean

    str = WorksheetFunction.Rept("A", 255)

    Set r1 = Range("A1")
    r1.Value = str
    xlWSFuncCheck1 = Excel.WorksheetFunction.IsError(r1)
    vbaInfCheck1 = VBA.Information.IsError(r1)

    str = str & "A"

    Set r2 = Range("A2")
    r2.Value = str
    xlWSFuncCheck2 = Excel.WorksheetFunction.IsError(r2)
    vbaInfCheck2 = VBA.Information.IsError(r2)
End Sub

The above procedure was written in Excel 2010 and validated in Excel 2007. The target application is currently running in Excel 2007.

1: Excel.WorksheetFunction.IsError ?

VBA.Information.IsError, , . ...

2: ?

Answer 1

Excel 2007, .

1: Is this a bug or is there a reasonable explanation for the behavior of Excel.WorksheetFunction.IsError in this case?

255 , . , , .

, a Value of Range, a String 255 , Range IsError Range false.

2: Is there a more reliable way to check for errors in a specific cell?

, CPearson, r2.Value r2 IsError ( VBA IsError), . , Range . - , . , . , ( , r2.Value).

Answer 2

, ".value".

, A1

=1-BINOM.DIST(35, 76, 0.1, TRUE)

, Excel 0.000000 .

, VBA

IsError(ActiveSheet.Range("A1").Value)

VBA TRUE. , , Excel , , .

, Excel - , VBA TRUE IsError(). , Excel,

=1-NORM.S.VERT(42, TRUE)

IsError().