Develop Customized Data Governance Solutions With Excel VBA

To develop customized Data Governance solutions in Excel VBA, the focus will be on creating a robust data validation system that ensures data integrity and compliance. Here’s a detailed guide, including the necessary code and explanations for each step:

1. Data Input Sheet

The Data Input Sheet will be where users input their data. This sheet will include various columns, such as:

• ID (Unique Identifier)
• Name (Text input)
• Age (Numeric input)
• Email (Email format validation)
• Date of Birth (Date validation)

2. VBA Code for Data Validation

The VBA code will perform checks on the input data to ensure that it follows the required rules, such as:

• Numeric Validation: Ensure that the ‘Age’ column contains only numeric values.
• Email Format Validation: Ensure that the ‘Email’ column follows a valid email format.
• Date Validation: Ensure that the ‘Date of Birth’ is in a valid date format and in the past.

Here is the VBA code for implementing these validations:

Sub ValidateData()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim ageCell As Range
    Dim emailCell As Range
    Dim dobCell As Range
    Dim validEmail As Boolean
    Set ws = ThisWorkbook.Sheets("DataInput") ' Name of your input sheet
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row ' Find the last row in the sheet
    For i = 2 To lastRow ' Start from row 2 assuming row 1 is headers
        ' Validate Age (Numeric)
        Set ageCell = ws.Cells(i, 3) ' Assuming Age is in column C
        If Not IsNumeric(ageCell.Value) Or ageCell.Value <= 0 Then
            ageCell.Interior.Color = RGB(255, 0, 0) ' Highlight invalid data in red
            MsgBox "Invalid Age in row " & i
        Else
            ageCell.Interior.ColorIndex = xlNone ' Remove highlight if valid
        End If
        ' Validate Email (Format Check)
        Set emailCell = ws.Cells(i, 4) ' Assuming Email is in column D
        validEmail = IsValidEmail(emailCell.Value)
        If Not validEmail Then
            emailCell.Interior.Color = RGB(255, 0, 0)
            MsgBox "Invalid Email in row " & i
        Else
            emailCell.Interior.ColorIndex = xlNone
        End If
        ' Validate Date of Birth (Must be a past date)
        Set dobCell = ws.Cells(i, 5) ' Assuming Date of Birth is in column E
        If Not IsDate(dobCell.Value) Or dobCell.Value >= Date Then
            dobCell.Interior.Color = RGB(255, 0, 0)
            MsgBox "Invalid Date of Birth in row " & i
        Else
            dobCell.Interior.ColorIndex = xlNone
        End If
    Next i
End Sub
' Function to check if email format is valid
Function IsValidEmail(email As String) As Boolean
    Dim regEx As Object
    Set regEx = CreateObject("VBScript.RegExp")
    regEx.IgnoreCase = True
    regEx.Global = True
    regEx.Pattern = "^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$"
    IsValidEmail = regEx.Test(email)
End Function

3. Button for Data Validation

To trigger the data validation process, you can add a button to the worksheet and assign the ValidateData macro to it.

Steps to Add the Button:

1. Go to the Developer tab (enable it if you don’t see it).
2. Click on Insert and choose the Button (Form Control).
3. Draw the button on the sheet.
4. Right-click the button, and select Assign Macro.
5. Choose ValidateData from the list of macros.

Now, whenever the button is clicked, it will trigger the ValidateData subroutine, which will validate all the rows in the Data Input Sheet.

4. Sample Output

When the data is validated, if any row has invalid data, the corresponding cell will be highlighted in red, and a message box will pop up with the row number where the issue is located.

Example Scenario:

• Row 2: Name: John, Age: -5 (Invalid), Email: john.doe@example, Date of Birth: 01/01/1990.
  • The Age cell will be highlighted red, and a message box will appear stating « Invalid Age in row 2. »
  • The Email cell will also be highlighted red, and a message box will appear stating « Invalid Email in row 2. »
  • The Date of Birth will be validated (assuming it’s a valid date, but if not, the cell will be highlighted in red).

Result:

• All invalid entries will have their cells highlighted in red, and you’ll receive a message box pointing out which row contains the error.

Explanation:

• Age Validation ensures that users enter a valid numeric value greater than 0.
• Email Validation uses a regular expression to ensure the email follows a valid format.
• Date of Birth Validation ensures that the date entered is a valid date and that it is in the past, as we typically wouldn’t want a future birthdate.

This setup allows you to efficiently implement data governance rules in Excel, ensuring the data being input is clean, valid, and compliant with the required formats.