How can I use VBA to ignore green triangle error in range without looping cell by cell?

Question

I have some large data sets that I am automating and distributing. I want to eliminate the little green triangles that warn the user about numbers stored as text. I have used the following code but it's VERY slow on massive sheets.

     Range(Cells(1, 1), Cells(lastrow, lColumn)).Select
     'kill those dang green cell triagles
     Dim rngCell As Range, bError As Byte
         For Each rngCell In Selection.Cells

             For bError = 3 To 3 Step 1

                 With rngCell
                     If .Errors(bError).Value Then
                         .Errors(bError).Ignore = True
                     End If
                 End With
             Next bError
         Next rngCell

As you can see I already cut it down to 1/7th of the time by not looping through every error just the one I am trying to prevent but it's still VERY slow.

Also I already know about

     Application.ErrorCheckingOptions.NumberAsText = False

But I don't want to use it as I do not want to change users system settings. I want the effect of the loop without looping through all cells. Can I some how tell Excel to stop checking an entire range without looping cell by cell?

Any effective and fast way to do this would be very helpful. Thank you in advance!!

Morpheus

It is completely reasonable to hide specific warnings in specific cells where you know they are not relevant warnings, while still maintaining the user's intent to have other warnings in other cells appear. – Fahrenheit

It is probably not going to have a big performance gain, but if you only want to ignore error 3, you can get rid of the For...Next loop. What is probably going to give more performance gain is turning of screen updating etc. See https://mcmap.net/q/763155/-how-to-speed-up-vba-code – Personify

Answer 1

The preferred solution would be to convert the string to a number before you bring it into Excel. For example, when I am working with SQL and I have some numerical values stored as a NVARCHAR in the database I will use a CONVERT(int, colName) in the SQL statement when I am bringing it into Excel. This brings it in as a number and I no longer get that message.

Now, if this sort of option isn't available to you, you can fix that error another way. Simply set the range of values that have the number stored as text error equal to itself.

For example:

Sub Test()

    Sheets("Sheet1").Range("A1:A3").Value = Sheets("Sheet1").Range("A1:A3").Value

End Sub

Where A1:A3 in this example is the range of values you want to no longer store as text.

Since your numbers have leading zeroes, you can change the formatting of these cells to add these zeroes as such:

Sub Test()

    Sheets("Sheet1").Range("A1:A3").Value = Sheets("Sheet1").Range("A1:A3").Value
    'This assumes your numbers are 11 digits long
    'Thus 11132 would display as 00000011132
    Sheets("Sheet1").Range("A1:A3").NumberFormat = "00000000000"

End Sub

This will change the display to show leading zeroes again. If you are exporting this data in any fashion you may have to take steps to ensure that this particular column is exported as text and not number, but I can't help much more without specifics.

Amadou

Thank you but I don't want it to be stored as a number. There are leading zeros that cannot drop off so it must remain text. What I need is to have Excel stop warning me. It's stored as text intentionally. Can you help? thank you again – Morpheus

You can update the .NumberFormat property of the range to display leading zeroes. I have updated my answer to reflect this. Though ultimately this would still store it as a number. If this method doesn't suit your needs I will have to think a bit further on it. – Amadou

Thanks @Soulfire, unfortunately, Converting in SQL doesn't work as Excel converts it and see's it as Number. Awesome workaround though! thank you. – Alcoholicity

Why is this the accepted answer? It does not solve the problem, and he misunderstands the question. – Fitful

Answer 2

The obvious answer (Range(...).Errors(3).Ignore = True) doesn't seem to work when the Range is larger than a single cell.

After a bit of experimentation, I found that you can manually select the range of cells and click the little pop-up menu that appears and tell it to ignore all errors in the range, but this doesn't appear to have a VBA equivalent.

Doing this experiment with the macro recorder on records nothing, which is usually a sign that the programmer at Microsoft who implemented this functionality was incompetent.

Sadly, I think this means that there is no solution other than looping.

Related

Fahrenheit

"the programmer at Microsoft who implemented this functionality was incompetent." This may be the truth :( Thank you for looking into this for me – Morpheus

Coming from the future "2024", maybe this simple needed piece of code still hasn't been developed. :| – So

Answer 3

You can use workbook events to turn on and off the user's system setting, and restore the setting back to the original value when you're done.

In the ThisWorkbook object, put an Open event that takes note of their initial setting and then turns it off.

Dim MyErrorCheckValue as Boolean

Private Sub Workbook_Open()
    MyErrorCheckValue = Application.ErrorCheckingOptions.NumberAsText
    Application.ErrorCheckingOptions.NumberAsText = False
End Sub

Add a BeforeClose event to set it back to original value when closing the file.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.ErrorCheckingOptions.NumberAsText = MyErrorCheckValue
End Sub

Then add Activate and Deactivate events so it switches when the user opens or views a different spreadsheet.

Private Sub Workbook_Activate()
    Application.ErrorCheckingOptions.NumberAsText = False
End Sub

Private Sub Workbook_Deactivate()
    Application.ErrorCheckingOptions.NumberAsText = MyErrorCheckValue
End Sub

You could add similar events at the sheet level to turn it on and off when switching sheets within the workbook.

Would also be wise to add some error handling that sets it back to original value so you don't accidentally leave it in the wrong state in the unlikely event that your code bugs out somewhere.

Reins

Unsure of this without testing it, but it seems correct, so I'm giving a +1 -- this really should be the accepted answer. If it's any consolation, this is the exact answer I was looking for. – Ticino

Answer 4

I have 12 types of errors in my Excel (Members of XlErrorChecks):

xlEmptyCellReferences = 8   
xlEvaluateToError = 2   
xlInconsistentFormula = 5   
xlInconsistentListFormula = 10  
xlListDataValidation = 9    
xlMisleadingFormat = 11  
xlNumberAsText = 4  
xlOmittedCells = 6  
xlOutdatedLinkedDataType = 12  
xlStaleValue = 1  
xlTextDate = 3  
xlUnlockedFormulaCells = 7

Official documentation help - https://learn.microsoft.com/pl-pl/office/vba/api/Excel.XlErrorChecks - is incorrect.

We should generalize the code for X ;) of errors

   Dim rngCell As Range, nr As Byte

   On Error Resume Next
   For Each rngCell In Selection.Cells

   nr = 0

     Do
     nr = nr + 1
      If rngCell.Errors.Item(nr).Value Then
        rngCell.Errors.Item(nr).Ignore = True
      End If
     Loop Until Err.Number <> 0

    Next rngCell

Chantilly

Answer 5

There appears to be an error in the code shared by the original poster. In order to get this to work I have add to add in the .Item of the error:

Dim rngCell As Range, bError As Byte
For Each rngCell In Selection.Cells

    For bError = 1 To 4

        With rngCell
            If .Errors.Item(bError).value Then
                .Errors.Item(bError).Ignore = True
            End If
        End With
    Next bError
Next rngCell

Complect

Answer 6

I have created a nifty procedure to put the error in the 'ignore' list: Have fun

Sub SetInconsistentFormulaErrorsFalse(rng As Range, _
Optional arrErrortypes As Variant = Null, _
Optional bIgnoreErrors As Boolean = True)

Dim cl As Range
Dim i As Integer

If IsNull(arrErrortypes) Then
   arrErrortypes = Array(1, 2, 3, 4, 5, 6, 7, 8, 9)
End If

For i = 0 To UBound(arrErrortypes) - 1
    For Each cl In rng.Cells
        cl.Errors(arrErrortypes(i)).Ignore = bIgnoreErrors
    Next
Next i


Set cl = Nothing

End Sub

Schiff

Answer 7

Usually you can get a better performance by using Application.ScreenUpdating:

Sub RemoveGreenTrianglesEx(ws As Worksheet, lColumn As Integer, bError As Byte)
Dim rng As Range, r As Range
Dim lastRow As Integer

    Application.ScreenUpdating = False
    lastRow = ws.UsedRange.Rows.Count
    Set rng = Range(Cells(1, 1), Cells(lastRow, lColumn))
    For Each r In rng
        With r
            If .Errors(bError).Value Then
                .Errors(bError).Ignore = True
            End If
        End With
    Next r
    Application.ScreenUpdating = True
    
End Sub

Sub RemoveGreenTriangles()

'Number as Text = 4
RemoveGreenTrianglesEx ActiveSheet, 1, 4

End Sub

Hibernal