Automate Your Workflow: Streamline Data Validation in Excel Using VBA Macros

Person typing on laptop

As a tax consultant, I’m constantly cross-checking records against GST data. This repetitive task can be time-consuming and error-prone when done manually in Excel. The solution? Automating the process with VBA macros.

The Challenge: Why Manual Data Validation is Time-Consuming

When working with large datasets, manual validation becomes impractical due to:

Human error and fatigue leading to missed discrepancies
Time-consuming nature of comparing records one by one
The need for consistent checks across multiple sheets or workbooks

Tools like CelTools can automate many aspects of data validation, but understanding the VBA approach gives you flexibility and control.

A Practical Example: Validating GST Records with a Macro on Workbook Open

Let’s create an example where we validate records in Column O of Sheet57 (MATRIZ3) whenever the workbook is opened. This macro will check for specific conditions and flag any discrepancies.

The Step-by-Step Solution: Writing Your VBA Macro

Open your Excel file and press ALT + F11 to open the Visual Basic Editor (VBE).
In VBE, find “ThisWorkbook” in the Project Explorer.
Double-click on “ThisWorkbook”, then select “Worksheet” from the left dropdown and “Open” from the right dropdown. This will create a new subroutine that runs when you open your workbook.


Private Sub Workbook_Open()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Hoja57")

    ' Validate records in Column O (15th column)
    Call ValidateRecords(ws, 15)

End Sub

Sub ValidateRecords(ByRef ws As Worksheet, ByVal colNumber As Integer)
    Dim cell As Range
    Application.ScreenUpdating = False

    For Each cell In ws.Columns(colNumber).Cells
        If IsEmpty(cell) Or Not IsNumeric(cell.Value) Then
            ' Flag invalid records (e.g., with a red background color or note in adjacent column)
            With cell.Interior
                .Color = RGB(255, 0, 0)   ' Red background for flagging errors
            End With

            ws.Cells(cell.Row, colNumber + 1).Value = "Invalid Record"
        ElseIf cell.Value < 0 Then
            ' Additional validation: Flag negative values as invalid records
            With cell.Interior
                .Color = RGB(255, 0, 0)   ' Red background for flagging errors
            End With

            ws.Cells(cell.Row, colNumber + 1).Value = "Negative Value"
        ElseIf cell.Value > 999 Then
            ' Additional validation: Flag unusually high values as potential outliers
            With cell.Interior
                .Color = RGB(255, 204, 0)   ' Yellow background for flagging warnings
            End With

            ws.Cells(cell.Row, colNumber + 1).Value = "High Value"
        Else
            ' Clear any previous flags if the record is valid now
            cell.Interior.ColorIndex = xlNone
            ws.Cells(cell.Row, colNumber + 1).ClearContents
        End If
    Next cell

    Application.ScreenUpdating = True
End Sub

For frequent users who need to validate data across multiple sheets or workbooks regularly, CelTools offers a more user-friendly interface for these tasks.

The Advanced Variation: Adding Custom Validation Rules and Notifications

You can extend the macro by adding custom validation rules based on your specific requirements. For example:

Check if values fall within certain ranges or match patterns (e.g., using regular expressions)
Send email notifications for flagged records, integrating with Outlook
Log the results to a separate sheet for auditing purposes

The CelTools add-in can handle these advanced scenarios without writing any code.

Avoid Common Mistakes and Misconceptions in VBA Data Validation

Not turning off screen updating: Always use `Application.ScreenUpdating = False` at the start of your macro to speed up execution, then set it back to True at the end.
Ignoring error handling: Add basic error-handling routines (`On Error Resume Next`) to prevent crashes if something unexpected happens during validation.
Overlooking performance optimization: For large datasets, consider processing data in chunks or using arrays instead of iterating through each cell individually.

Tools like CelTools can help avoid these pitfalls by providing optimized built-in functions for common validation tasks.

The VBA Solution: Automating Data Validation with a Macro on Workbook Open


Private Sub Workbook_Open()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Hoja57")

    ' Validate records in Column O (15th column)
    Call ValidateRecords(ws, 15)

End Sub

Sub ValidateRecords(ByRef ws As Worksheet, ByVal colNumber As Integer)
    Dim cell As Range
    Application.ScreenUpdating = False

    For Each cell In ws.Columns(colNumber).Cells
        If IsEmpty(cell) Or Not IsNumeric(cell.Value) Then
            ' Flag invalid records (e.g., with a red background color or note in adjacent column)
            With cell.Interior
                .Color = RGB(255, 0, 0)   ' Red background for flagging errors
            End With

            ws.Cells(cell.Row, colNumber + 1).Value = "Invalid Record"
        ElseIf cell.Value < 0 Then
            ' Additional validation: Flag negative values as invalid records
            With cell.Interior
                .Color = RGB(255, 0, 0)   ' Red background for flagging errors
            End With

            ws.Cells(cell.Row, colNumber + 1).Value = "Negative Value"
        ElseIf cell.Value > 999 Then
            ' Additional validation: Flag unusually high values as potential outliers
            With cell.Interior
                .Color = RGB(255, 204, 0)   ' Yellow background for flagging warnings
            End With

            ws.Cells(cell.Row, colNumber + 1).Value = "High Value"
        Else
            ' Clear any previous flags if the record is valid now
            cell.Interior.ColorIndex = xlNone
            ws.Cells(cell.Row, colNumber + 1).ClearContents
        End If
    Next cell

    Application.ScreenUpdating = True
End Sub

Technical Summary: Combining Manual Skills with Specialized Tools for Optimal Results

The combination of manual VBA programming and specialized tools like CelTools provides a powerful approach to data validation in Excel. While writing custom macros gives you the flexibility to tailor solutions exactly as needed, using add-ins can save time on common tasks.