In my current version of Excel, the xlNumberAsText enumeration seems to be incompatible with both the XlErrorChecks Enumeration (Excel) documentation and the previous SO recommendation. Particular error seems to be point 4; whereas previous messages about clearing this error using VBA used constant 3.
Is this a bug in Excel 2016? Or am I doing something wrong.
Here is the code demonstrating the problem. And if you look at the worksheet after running the code, it is obvious that the error is marked in Excel as NumberAsText, not as Inconsistent Formula.
Option Explicit
Sub foo()
Dim I As Long
Dim B As Boolean
Dim S As String
Dim R As Range
'Save current state
B = Application.ErrorCheckingOptions.NumberAsText
'Enable
Application.ErrorCheckingOptions.NumberAsText = True
Set R = Cells(1, 1)
With R
.Clear
.NumberFormat = "@"
.Value = "1"
End With
For I = 1 To 10
S = S & vbLf & I & vbTab & R.Errors(I).Value
Next I
S = Mid(S, 2)
'Restore original state
Application.ErrorCheckingOptions.NumberAsText = B
MsgBox S
End Sub
And also, trying to clear the error window, I have to use
R.Errors(4).Ignore = True
Neither one Errors(3), nor Errors(xlNumberAsText)will not affect the error window.
:
Inconsistent Formula: 5
Wrong Data Type: 2
. MS. , .