VBA Verifying Data

[KX13ZN]

I have had a look around for a way to check that the data stored in a range is only numbers or is blank. However nothing matches what I need...at least in VBA, which is what I need.
The range can be anywhere between A1 to D65000. The data will always end up in the first 4 columns.


Theoretically, in column A there should only be whole numbers between 1 and 999,999,999 (there whole range isn't present but values can go that high) and blank cells.

However if something has gone wrong in a previous process then the cells may contain dates or letters or both as well as numbers.
I've tried using data validation within excel but that doesn't check existing data if applied after the fact and because the data moves around on the sheet setting it before hand or setting cell formatting before isn't practical.
And IsNumber or IsNumeric require the cells to be formatted to work properly...as far as I can tell.

So unless excel has auto formatted a cell to be something specific, then all the cells are formatted as general.

So I need to way to check that just the numbers 0,1,2,3,4,5,6,7,8,9 are present and that its not a date.

I then need to check if all cells in Column B contain letters, but these are names so they contain spaces and accented letters, just not numbers, period or commas

I also need to check that Column C only contains capital Letters, so A-Z only, or be blank
Has to be case sensitive.

And then lastly Column D should only contain dates or be blank

1​	name one	AAAA	22/01/2020​
22​	name two	BBBB	21/01/2020​
333​	name threé	CCCC	20/01/2020​
4444​	name four	DDDD	19/01/2020​
55555​	name fivé	EEEE	18/01/2020​
666666​	name six	FFFF	17/01/2020​
7777777​	name seven	GGGG	16/01/2020​
88888888​	name eight	HHHH	15/01/2020​
999999999​	name nine	IIIII	14/01/2020​

If any errors are found then it just needs to output a msgbox and then run some other code because it means a previous process has gone wrong and things need to start again.

I've never worked directly with data checking so I'm completely new to this.
Help would be great but I also want to learn how to do this in the future as i'm sure i'll need to check otherthing later on.

 

[Yongle]

Start with this
- suggest you test with about 20 rows of problem data first to ensure that it getting the main checks right
- then test with a few hundred rows before testing with a few thousand (it may take a long time to run)
- after testing I am sure it will need a few amandments
- remember that adding more tests slows the code

It works with my data
- but I typed everything into the worksheet
- several assumptions are implied in the way I built the tests
- but there is no telling what will happen with your data
- dates can be particularly problematic
- let's see what works with the data that is dumped to your worksheet

Let me know if you need help modifying the tests to work with your data

I am curious; why is the range A1:D65000 ?
- that sounds like a throwback constraint on number of rows available before Excel2007
- perhaps it an old system dumping data to Excel

Place VBA in a standard module but run it from the sheet containing the data

Sub Verify()
    Const r = 2                 ' the first row to check for value
    Const V = vbCr & vbCr
    Dim A, B, C, D, x As Long, F As String
    Dim Rng As Range, Msg As String, valueD As Date
   
CreateArrays:
    With ActiveSheet
        Set Rng = .Range("A1", .Cells(Rows.Count, 1).End(xlUp))
    End With
    A = Rng
    B = Rng.Offset(, 1)
    C = Rng.Offset(, 2)
    D = Rng.Offset(, 3)

ColumnA:
    On Error Resume Next
    For x = r To UBound(A)
        F = Evaluate("CELL(" & Chr(34) & "Format" & Chr(34) & ",A" & x & ")")
        If CLng(Val(A(x, 1))) <> A(x, 1) Then
            Msg = "Bad integer" & V & A(x, 1): GoTo Handling
        ElseIf F Like "D*" Then Msg = "Formatted as date " & V & Cells(x, 1).Address(0, 0): GoTo Handling
        End If
    Next x
    On Error GoTo 0

ColumnB:
    For x = r To UBound(B)
        If B(x, 1) Like "*[0-9]*" Or B(x, 1) Like "*,*" Or B(x, 1) Like "*.*" Then Msg = "Bad Name" & V & B(x, 1): GoTo Handling
    Next x

ColumnC:
    For x = r To UBound(C)
        If UCase(C(x, 1)) <> C(x, 1) Then Msg = "Not Upper Case" & V & C(x, 1): GoTo Handling
        If C(x, 1) Like "*[0-9]*" Or C(x, 1) Like "*,*" Or C(x, 1) Like "*.*" Then Msg = "Not Upper Case" & V & C(x, 1): GoTo Handling
    Next x
   
ColumnD:
    On Error Resume Next
    For x = r To UBound(D)
        If D(x, 1) <> "" And Left(Evaluate("CELL(" & Chr(34) & "Format" & Chr(34) & ",D" & x & ")"), 1) <> "D" Then Msg = "Bad date" & V & Cells(x, 4).Address(0, 0): GoTo Handling
    Next x
    On Error GoTo 0

Exit Sub

Handling: MsgBox "Row " & x & V & Msg, vbExclamation, ""

End Sub

 

[Sunjinsak]

Hi KX13ZN. There is probably a much more elegant and efficient way to do this, but the following will work...

Edit: @Yongle beat me to it

Option Explicit

Sub TestDataValidation()

    'First we create an array to hold all the data
    Dim DataArray() As Variant
    DataArray() = Range("A1:D9").Value 'We're using the range containing data in the example but you can change this to any range you need
  
    'Then we can loop though the array and test each row, column by column
    Dim col As Integer, row As Integer 'Counters for our loop
  
    'Loop through each 'column' in the array...
    For col = 1 To UBound(DataArray(), 2)
  
        'Loop through each 'row' in the 'column'...
        For row = 1 To UBound(DataArray(), 1)
          
            'Check which 'column' we're working with
            Select Case col
          
                Case 1
                    'If the value in the cell is not numeric...
                    If Not IsNumeric(DataArray(row, col)) Then
                        MsgBox "The value in Column A, Row " & row & " is not numeric"
                        Exit Sub
                    End If
                  
                Case 2
                    'If the value in the cell contains an invalid character
                    If ContainsInvalidChars(CStr(DataArray(row, col))) Then
                        MsgBox "The value in Column B, Row " & row & " contains an invalid character"
                        Exit Sub
                    End If
                  
                Case 3
                    'If the value in the cell is not all uppercase characters...
                    If Not IsAllCaps(CStr(DataArray(row, col))) Or Not IsAlpha(CStr(DataArray(row, col))) And Not IsEmpty(DataArray(row, col)) Then
                        MsgBox "The value in Column C, Row " & row & " is not all uppercase letters"
                        Exit Sub
                    End If
                  
                Case 4
                    'If the value in the cell is not a date and is not empty...
                    If Not IsDate(DataArray(row, col)) And Not IsEmpty(DataArray(row, col)) Then
                        MsgBox "The value in Column D, Row " & row & " is not a valid date"
                        Exit Sub
                    End If
              
                Case Else
                    'There are more than 4 'columns' in the array. This is where you would handle for that if necessary.
                    Exit Sub
                  
            End Select
              
        Next row
  
    Next col
  
    MsgBox "Data validation complete. Everything is cool."

End Sub


Public Function IsAllCaps(ByVal stringToTest As String) As Boolean
' This function returns True if stringToTest contains only capital letters in the range A-Z, False otherwise

    IsAllCaps = stringToTest = UCase(stringToTest)

End Function


Public Function IsAlpha(ByVal stringToTest As String) As Boolean
' This function returns True if stringToTest contains only letters in the range A-Z, False otherwise

    IsAlpha = stringToTest Like WorksheetFunction.Rept("[a-zA-Z]", Len(stringToTest))
  
End Function


Public Function ContainsInvalidChars(ByVal stringToTest As String) As Boolean
' This function returns True if stringToTest contains a period, a comma or any numeric character, False otherwise

    Dim invalidChars As Object
    Set invalidChars = CreateObject("VBScript.RegExp")
    invalidChars.Pattern = "[0-9,.]"
  
    ContainsInvalidChars = invalidChars.test(stringToTest)

End Function

 

[Yongle]

@Sunjinsak - that's what usualy happens to me
It is very satisfying to be first this time even if only by 2 minutes!

 

[KX13ZN]

Thank you so much, both work perfectly. I'm also glad they don't throw out hundreds of errors and stop on the first, thank you.
I also appreciate the commenting to explain the code Sunjinsak. That will definitely help me in my learning.

Will have to create some lines tho to remove the data validation that is set after the code has run.

 

[KX13ZN]

I've just hit a snag with your code Sunjinsak

I set the range to my sheet which is A2:D65000 but I get an overflow error. I'm pretty sure excel 2010 can handle upto 65536 rows, so im not sure whats going on, any ideas?

 

[Yongle]

I've just hit a snag with your code @Sunjinsak

I set the range to my sheet which is A2:D65000 but I get an overflow error. I'm pretty sure excel 2010 can handle upto 65536 rows, so im not sure whats going on, any ideas?

Try ..
Dim col As Long, row As Long

Your row numbers are too big for the Integer variable (max for integer = 32,767)
read :

Data type summary

Office VBA reference topic

docs.microsoft.com

Why A2:D65000 ?
Why make VBA do work on thousand of empty rows ?
Try this instead - uses column A last value to determine range

Range("A2:D" & Cells(Rows.Count, "A").End(xlUp).Row)

 

[KX13ZN]

Thanks Yongle, that has solved the problem and it works but now i can't get it to run from another sheet, the sheet its run on has to be selected.

I also tried your code on my data Yongle and it works fine, all the checks throw out the errors in the correct place, it also runs pretty quickly. I also can't however get it to work without having the sheet selected. I need it to effectively run in the background. I tried editing the 'with activesheet' but for some reason that was not enough to change where the code runs, do i have to add a sheet reference to every mention of cells?

Also as for why 65000, its just a random number i came up with to set as max to stop other bits of code im using from running on all 1million rows so I've stuck with it and use it when testing code out.

 

[Sunjinsak]

Sorry about the overflow error. That was my mistake - I should have spotted it. Easily done.

Try replacing:

Range("A2:D65000").Value

with:

Worksheets("NameOfSheet").Range("A2:D65000").Value

(replacing "NameOfSheet" with the actual name of the sheet your data is held on), or if using @Yongle's version (which is a good idea because it allows the range to have a dynamic number of rows) it would be:

Worksheets("NameOfSheet").Range("A2:D" & Cells(Rows.Count, "A").End(xlUp).Row)

 

[KX13ZN]

I wouldn't even think of holding it against you, thank you for providing me with a solution.

I've managed to get your code working just then way I need it to Sunjinsak. Thank you.

Just out of curiosity, is it possible to add DoEvents, into the code to break it up and stop excel from becoming unresponsive for a couple of seconds?