Ignore 'Number Stored as Text' Error in Excel programmatically using VBA

Question

I want to store text such as '001234' in a cell. I have set the number format of this cell to text. After storing, an error 'Number Stored as Text' is shown in the form of a green triangle at the top left corner of the cell. It is very disturbing and I want it to be removed programmatically.

Gary's Student · Answer 1 · 2017-08-25T12:12:42.667

23

Consider:

Sub Macro1()
    Application.ErrorCheckingOptions.NumberAsText = False
End Sub

This is a single line of a much larger macro to configure Excel when I begin working on a new computer.

edited Aug 25 '17 at 12:12

answered Aug 19 '13 at 15:36

Gary's Student

95,722
10
59
99

+1 I tried looking for that a while ago. The following wouldn't work for me: `Application.Range(Item.Address).Errors.Item(xlNumberAsText).Ignore = True`. Thank you! – Brian Aug 25 '17 at 10:55
@Brian See my **EDIT** – Gary's Student Aug 25 '17 at 12:13
This changes the global Excel settings of the user, which is probably not a good idea to do in a general purpose application program. – Christian d'Heureuse Jul 19 '23 at 03:06

score 6 · Answer 2 · edited Jul 22 '19 at 15:06

6

The above examples turn off the "number stored as text" checking for the application.

This code will turn it off for a particular cell:

range("G93").Errors.Item(xlnumberastext).Ignore = True

edited Jul 22 '19 at 15:06

Gufran Hasan

8,910
7
38
51

answered Jul 22 '19 at 14:48

James Hammontree

61
1
1

score 3 · Answer 3 · edited Oct 21 '17 at 06:52

3

You can select the range you want to work and then put for example (now added speechmarks):

Range("A1:Z20").Application.ErrorCheckingOptions.NumberAsText = False

edited Oct 21 '17 at 06:52

Community

1
1

answered Jun 15 '16 at 10:17

user6468957

39
1

2

That seems to turn it off for the entire workbooks, and not limit it only to the particular range. – Ron Rosenfeld Mar 01 '18 at 13:31
1

I agree with your observation, because the Range object has an Application property which refers to the Application in which that range exists. That line is essentially the same as just typing Application.ErrorCheckingOptions.NumberAsText = False – Ejaz Ahmed Apr 10 '18 at 18:28

score 1 · Answer 4 · answered Aug 19 '13 at 15:41

1

Alternately, instead of formatting the cell as text, you could format the cell as 000000 to get the leading 0's to show.

answered Aug 19 '13 at 15:41

tigeravatar

26,199
5
30
38

There may be good reason for storing the number as text, though, such as matching using VLOOKUP etc. – Matti Wens Jan 23 '18 at 16:25

Ignore 'Number Stored as Text' Error in Excel programmatically using VBA

4 Answers4

Linked