How can I use VBA to ignore the green triangle error in a range without looping the cell by cell?
I have several large datasets that I am automating and distributing. I want to eliminate the little green triangles that warn the user about numbers stored as text. I used the following code but it is 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've already cut it down to 1 / 7th time, not skipping every error, just the one I'm 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 don’t want to change user system settings. I want the loop effect not to overlap across all cells. Can I tell how Excel stops checking the entire range without looping cell by cell?
Any efficient and quick way to do this will be very helpful. Thanks in advance!
source to share
The preferred solution would be to convert the string to a number before you bring it to Excel. For example, when I am working with SQL and I have numeric values stored both NVARCHAR
in a database, I will use CONVERT(int, colName)
in the SQL statement when I cast it in Excel. This brings it to a number and I don't get this message anymore.
Now, if this option is not available to you, you can fix this error in another way. Just set a range of values, the number of which is stored as a 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 that you want to no longer be stored as text.
Since your numbers are null values, you can change the formatting of these cells to add those nulls 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 display leading zeros again. If you export this data in any way, you might have to take steps to ensure that this particular column is exported as text and not a number, but I can't help much more without any specifics.
source to share
The obvious answer ( Range(...).Errors(3).Ignore = True
) doesn't seem to work when the range is larger than one cell.
After some experimentation, I found that you can manually select a range of cells and click on the little popup menu that appears and tell it to ignore all errors in the range, but that doesn't seem to have a VBA equivalent.
Performing this experiment with a macro recorder does not record anything, which is usually a sign that the programmer at Microsoft who implemented this functionality was incompetent.
Unfortunately, I think this means that there is no solution other than a loop.
source to share
You can use workbook events to turn custom system settings on and off and restore the settings to their original value when you're done.
In the ThisWorkbook object, place an Open event that takes note of their initial setting and then disables it.
Dim MyErrorCheckValue as Boolean
Private Sub Workbook_Open()
MyErrorCheckValue = Application.ErrorCheckingOptions.NumberAsText
Application.ErrorCheckingOptions.NumberAsText = False
End Sub
Add a BeforeClose event to revert it to its original value when the file is closed.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.ErrorCheckingOptions.NumberAsText = MyErrorCheckValue
End Sub
Then add the Activate and Deactivate events so that it toggles when the user opens or views another spreadsheet.
Private Sub Workbook_Activate()
Application.ErrorCheckingOptions.NumberAsText = False
End Sub
Private Sub Workbook_Deactivate()
Application.ErrorCheckingOptions.NumberAsText = MyErrorCheckValue
End Sub
You can add similar events at the sheet level to turn it on and off when switching sheets in a workbook.
It would also be wise to add some error handling that will revert it to its original value so you don't accidentally leave it in the wrong state in the unlikely event that your code goes missing somewhere.
source to share
I created a great routine to put the error on 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
source to share