Data Validation Macro

Dear Experts,

Please I need your support in writing a simplified VBA code that checks data is a range of cells and report data that don't meet a set validation criteria which can be customized based on datatype/category and give an option to transfer the invalid data to a new sheet. The data range should also be customizable.
Iretioluwa OlawuyiRTM AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Máté FarkasDatabase Developer and AdministratorCommented:
This expert suggested creating a Gigs project.
This sounds like a project not a question.
Please create a GIG project and hire somebody to implement this for you.
1
Fabrice LambertFabrice LambertCommented:
Hi,

For some reasons, I can't upload any file, so I'll do my best to explain:

You can take advantage of inheritance to solve this.
First, create a class module and name it IValidator, this will be our base interface for validation rules:
Option Explicit

Public Function validate(ByVal data As String, ByRef reason As String) As Boolean
End Function

Open in new window

Second, we need a class implementing the interface, that will represent a particular validation rule. Like validator_integer that will check if the data given is an integer:
Option Explicit
Implements IValidator

Private Function IValidator_validate(ByVal data As String, reason As String) As Boolean
On Error GoTo Error
    Dim value As Integer
    
    value = CInt(data)
    If (CStr(value) = data) Then
        IValidator_validate = True
    Else
        reason = data & " is not an integer."
        IValidator_validate = False
    End If
Exit Function
Error:
    reason = data & " is not an integer."
    IValidator_validate = False
End Function

Open in new window

Third: we write our actual function, that take a range, a collection of IValidator, a flag notifying if data will have to be reported in a new sheet as parameters, and return a collection of string representing our error report:
Public Function validate(ByRef rng As Excel.Range, ByRef validators As Collection, Optional ByVal toNewSheet As Boolean = False) As Collection
        '// copy range value to an array for performances purpose
    Dim data() As Variant
    data = rng.value
    
        '// collection of errors
    Dim reasons As Collection
    Set reasons = New Collection
    Dim i As Long
        '// loop over all values
    For i = LBound(data, 1) To UBound(data, 1)
        Dim j As Long
        For j = LBound(data, 2) To UBound(data, 2)
            Dim validator As IValidator
                '// apply all validators
            For Each validator In validators
                Dim reason As String
                If Not (validator.validate(CStr(data(i, j)), reason)) Then
                    reasons.Add reason
                End If
            Next
        Next
    Next
    Set validate = reasons
        '// report to a new sheet if needed
    If (toNewSheet) Then
        outputReportToNewSheet reasons
    Next
End Function

Open in new window

Fourth, by respect to SRP (Single Responsibility Principle) we write a standalone procedure that will output the error report to a new sheet:
Private Sub outputReportToNewSheet(ByRef resons As Collection)
    Dim wb As Excel.Workbook
    Set wb = ThisWorkbook
    
    Dim ws As Excel.Worksheet
    Set ws = wb.Worksheets.Add
    
    Dim rng As Excel.Range
    Set rng = ws.Range("A1")
    
    Dim reason As Variant
    For Each reason In reasons
        rng.value = reason
        Set rng = rng.Offset(rowOffset:=1)
    Next
    Set rng = Nothing
    Set ws = Nothing
    Set wb = Nothing
End Sub

Open in new window

Finally, we need a test procedure
Public Sub test()
    Dim wb As Excel.Workbook
    Set wb = ThisWorkbook
    
    Dim ws As Excel.Worksheet
    Set ws = wb.Worksheets(1)
    
    Dim rng As Excel.Range
    Set rng = ws.Range("A1:A10")
    
    Dim validators As Collection
    Set validators = New Collection
    validators.Add New validator_integer
    
    Dim reasons As Collection
    Set reasons = validate(rng, validators, True)
    
    Dim reason As Variant
    For Each reason In reasons
        Debug.Print reason
    Next
    
    Set reasons = Nothing
    Set validators = Nothing
    Set rng = Nothing
    Set ws = Nothing
    Set wb = Nothing
End Sub

Open in new window

1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Iretioluwa OlawuyiRTM AnalystAuthor Commented:
Great response.
I appreciate your help, however, I will like to request a sample data that can fit into the code structure for me to understand clearly how it works and also apply to the data I need to validate, you can share to my email iretex1@gmail.com.
Thanks again.
0
Fabrice LambertFabrice LambertCommented:
Hmm,

you should award credit to experts who answered / provided help to your issue.
1
Iretioluwa OlawuyiRTM AnalystAuthor Commented:
Pardon me pls, Fabrice, I thought I did this before but somehow got skipped.
I appreciate your support.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.