VBA Excel Won't Ignore "Number Stored as Text" Error

Question

I think Excel is going senile.

For the life of me, I can't get my Excel VBA macro to ignore a "Number Stored as Text" error.

In cell C71 on a worksheet called "Main," I have the value 6135413313, which Excel is warning is a number stored as text. And it should be. But I want to remove that annoying little triangle at the end of my macro.

I've reduced my macro code to the bare bones for testing purposes, but that triangle still persists. Here's my macro:

Sub test()
    Range("C71").Errors(xlEvaluateToError).Ignore = True
End Sub

How is this not making that error go away? I've also tried Range("Main!C71"). That didn't work either.

This should be mind-bogglingly easy, but that one line of code still doesn't work. Any ideas?

Range is implicitly working on the ActiveSheet. Is that your intention? — ThunderFrame
– ThunderFrame, Commented Apr 28, 2017 at 1:28
I suspect that the Range.Errors property is read-only. (The documentation says it returns something, but doesn't say you can set anything.) I think you are looking for Application.ErrorCheckingOptions.NumberAsText = False — YowE3K
– YowE3K, Commented Apr 28, 2017 at 1:29
Did either of the answers solve your query? If so, please mark them appropriately to close this question. — VirtualMichael
– VirtualMichael, Commented Jul 22, 2017 at 8:31

Maddy · Accepted Answer · 2017-04-28 11:40:26Z

2

you can try this

Sub test()
Sheets("Main").Range("C71").Errors(xlNumberAsText).Ignore = True
End Sub

or

Sub test()
Sheets("Main").Range("C71").Value = Sheets("Main").Range("C71").Value
End Sub

or

The other way is you can manually disable background error checking.
you can find this option by clicking File - Excel Options - Formulas and uncheck the option

it will disable error checking for all cells

background error checking

edited Apr 28, 2017 at 11:40

answered Apr 28, 2017 at 11:29

Maddy

7716 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ExpertNovice · Accepted Answer · 2023-12-18 02:58:26Z

1

"Selection.Errors(xlTextDate).Ignore = True" will not work. To correct replace (xlTextDate) with one of the following values

(3)
(xlTextDate)
(xlNumberAsText - 1)

However, check the various values as your version of Excel may be different. I am using Excel 365.

Source: https://learn.microsoft.com/en-us/office/vba/api/Excel.XlErrorChecks On that page every variable list the needed value but not what Excel has assigned.

"Variable", "reported/needed", "Actual Excel Value"
xlEmptyCellReferences, 7, 8
xlEvaluateToError, 1, 2
xlInconsistentFormula, 4, 5
xlInconsistentListFormula, 9, 10
xlListDataValidation, 8, 9
xlNumberAsText, 3, 4
xlOmittedCells, 5, 6
xlTextDate, 2, 3
xlUnlockedFormulaCells, 6, 7

I don't make up the rules! When the values are corrected our code will likely fail again. I would report but MS has never fixed an error I have reported.

Reason to use text format: storing Facebook Video Id "4592884094151437" becomes "4592884094151430" doesn't work.

edited Dec 18, 2023 at 2:58

answered Dec 18, 2023 at 2:51

ExpertNovice

614 bronze badges

2 Comments

Drexel Over a year ago

Maybe it's only an issue in the 365 version, I had the same problem, it is not mentioned anywhere in older code snippets. Costed 2 hours of my life, so thank you for sharing this.

ExpertNovice Over a year ago

Your welcome. It could be a bug that only exists in 365 but I would be surprised if it was. They added three more XLErrorChecks at some point. 01 xlStaleValue (aka the culprit) 11 XlMisleadingFormat 12 XlOutdatedLinkedDataType

VirtualMichael · Accepted Answer · 2017-04-28 06:33:25Z

0

Cycling through each cell in the range to test the xlNumberAsText error and set the ignore flag works (although if you have a large number of cells, this may be slow).

Sub test2()
    Call turnOffNumberAsTextError(Sheets("Main").Range("C71"))
End Sub

Sub turnOffNumberAsTextError(rge As Range)
    Dim rngCell As Range
    For Each rngCell In rge.Cells
        With rngCell
            If .Errors.Item(xlNumberAsText).Value Then
                .Errors.Item(xlNumberAsText).Ignore = True
            End If
        End With
    Next rngCell
End Sub

answered Apr 28, 2017 at 6:33

VirtualMichael

7219 silver badges18 bronze badges

Collectives™ on Stack Overflow

VBA Excel Won't Ignore "Number Stored as Text" Error

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related