Error Indicators in Cells

[Trevor]

Can the subject error indicators be accessed through VBA & how?

 

[Alan Pong]

in excel 97, (e.g. A1 = 0, B1 = 1/A1), in debug window:
?cverr(activesheet.range("b1"))
Error 2007

does this help?
--END

 

[Trevor Fernandes]

Not really.

What I was trying to access is the error indicators like "The number in
this cell if formatted as text...."

Thanks

 

[Dave Peterson]

Take a look at ErrorCheckingOptions in VBA's help.

From there, you can click on "see also" and go to "Error Object" and see code
like this:


Option Explicit
Sub testme01()
Dim myCell As Range

Set myCell = ActiveSheet.Range("B3")

If myCell.Errors.Item(xlNumberAsText).Value = True Then
MsgBox "Might be an error"
Else
MsgBox "nope, not even a warning"
End If

End Sub

 

[Tom Ogilvy]

Just note that these were added in xl2002 and would not be available in any
earlier version (if you are writing code to distribute).

 

[Trevor Fernandes]

In the KB article Q291047 it is stated:

Method 1: Use the Error Button
If the cells in which numbers are displayed as text contain an error
indicator in the upper-left corner, follow these steps:
Click the cell that contains the error indicator.
Click the error button next to the cell, and then click Convert to
Number on the shortcut menu

How can I do the same thing through VBA?

Thanks
Trevor

 

[Dave Peterson]

One way is to just reset the numberformat to general and plop the value back in
there:

Option Explicit
Sub testme01()

With activesheet.Range("b9")
'just test data!
.NumberFormat = "@" 'make it text
.Value = 1234

'do the real work
.NumberFormat = "General"
.Value = .Value

End With

End Sub