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?
 
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
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.