Automate Excel Record Validation with VBA When Opening a Workbook

Written By: Ada Codewell – AI Specialist & Software Engineer at Gray Technical

As an accountant or financial analyst, you often need to validate records in Excel workbooks when they’re opened. This is crucial for ensuring data integrity and accuracy before any further analysis or reporting.

Spreadsheet closeup with numbers

Why This Problem Happens

The need to validate records upon opening a workbook arises from the necessity of ensuring data integrity. Manual validation is time-consuming and prone to human error, especially when dealing with large datasets.

CelTools, an Excel add-in for auditing, formulas, and automation (learn more here), can simplify this process by automating repetitive tasks like record validation. It’s particularly useful when you need to ensure consistency across multiple workbooks.

Step-by-Step Solution: Creating a VBA Macro for Record Validation on Workbook Open

The following steps guide you through creating a VBA macro that validates records in Column O of “Hoja57” (MATRIZ3) when the workbook is opened:

Open Excel and Press Alt + F11 to open the Visual Basic for Applications editor.
In the Project Explorer, find your current workbook under “VBAProject (YourWorkbookName).”
Double-click on “ThisWorkbook” in the list of objects. This will open a code window where you can write VBA macros.

The following is an example macro that validates records:

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

    ' Loop through each cell in Column O starting from row 2 (assuming headers are in row 1)
    Dim i As Long
    For i = 2 To ws.Cells(ws.Rows.Count, "O").End(xlUp).Row

        ' Example validation: Check if the value is not empty and meets a specific criteria
        If IsEmpty(ws.Cells(i, "O")) Or Not ValidateRecord(ws.Cells(i, "O").Value) Then
            MsgBox "Invalid record found in cell O" & i, vbExclamation
            Exit Sub ' Optionally stop further processing or highlight the invalid records
        End If

    Next i

    MsgBox "All records validated successfully!", vbInformation
End Sub

Function ValidateRecord(record As Variant) As Boolean
    ' Example validation logic: Check if record is a number and greater than 0
    On Error Resume Next
    ValidateRecord = IsNumeric(record) And CInt(record) > 0
    On Error GoTo 0
End Function

Explanation:

The Workbook_Open() subroutine runs automatically when the workbook is opened.
A reference to “Hoja57” worksheet is set using Set ws = ThisWorkbook.Sheets("Hoja57"). Adjust this if your sheet name differs.
The macro loops through each cell in Column O starting from row 2. It uses the End(xlUp).Row method to find the last used row dynamically, ensuring it works with varying amounts of data.
Validation Logic:
```
If IsEmpty(ws.Cells(i, "O")) Or Not ValidateRecord(ws.Cells(i, "O").Value) Then
    MsgBox "Invalid record found in cell O" & i, vbExclamation
```
The macro checks if the value is empty or fails a custom validation function. You can modify ValidateRecord() to fit your specific criteria.
Feedback:
```
MsgBox "All records validated successfully!", vbInformation
```
A message box informs you whether all records are valid, or if an invalid record is found.

Advanced Variation: Highlighting Invalid Records Instead of Stopping Execution

Instead of stopping execution when encountering an error:

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

    ' Loop through each cell in Column O starting from row 2 (assuming headers are in row 1)
    Dim i As Long
    For i = 2 To ws.Cells(ws.Rows.Count, "O").End(xlUp).Row

        If IsEmpty(ws.Cells(i, "O")) Or Not ValidateRecord(ws.Cells(i, "O").Value) Then
            ' Highlight invalid records in red font color instead of stopping execution
            With ws.Range("O" & i)
                .Font.Color = vbRed
                .Interior.Color = RGB(255, 218, 218) ' Light Red background for emphasis
            End With

        ElseIf ws.Cells(i, "O").Font.Color  xlNone Then
            ' Reset color if the record is now valid (optional)
            ws.Range("O" & i).Font.Color = vbBlack
            ws.Range("O" & i).Interior.ColorIndex = xlNone
        End If

    Next i

End Sub

This variation highlights invalid records in red and adds a light red background for emphasis. It continues processing all rows, making it easier to review multiple issues at once.

Coding on laptop in office

Common Mistakes or Misconceptions

Not Testing the Macro:

Always test your macro with a sample dataset before deploying it to live workbooks. This ensures that validation logic works as expected and doesn’t cause unintended interruptions.

Overlooking Edge Cases:

The example assumes all records are numeric values greater than 0. Adjust the ValidateRecord() function based on your specific criteria (e.g., checking for text patterns, date formats).

Ignoring Performance Concerns:

For very large datasets, running this macro could slow down workbook opening times. Consider optimizing validation logic or using more efficient data structures if performance becomes an issue.

Optional VBA Version for Formula-Based Validation

If you prefer formula-based validation:

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

    ' Add a helper column with the validation result (e.g., Column P)
    With ws.Range("P2:P" & ws.Cells(ws.Rows.Count, "O").End(xlUp).Row)
        .FormulaArray = "=IF(AND(NOT(ISBLANK(O2)), ISNUMBER(--O2), --O2 > 0), ""Valid"", ""Invalid"")"
    End With

    ' Loop through the helper column to find invalid records
    Dim i As Long
    For i = 2 To ws.Cells(ws.Rows.Count, "P").End(xlUp).Row
        If ws.Range("P" & i).Value = "Invalid" Then
            MsgBox "Invalid record found in cell O" & (i - 1), vbExclamation ' Adjust row reference if needed
            Exit Sub
        End If

    Next i

    MsgBox "All records validated successfully!", vbInformation
End Sub

This version uses an array formula to validate each record, storing results in a helper column. The macro then checks this helper column for any invalid entries.

A Technical Summary: Combining Manual Skills with Specialized Tools

In conclusion, automating Excel record validation using VBA significantly enhances data integrity and saves time. While manual methods provide a foundational understanding of the process, specialized tools like CelTools offer advanced features for frequent users.

Learn more about CelTools here

The combination of VBA automation and tool-assisted validation provides a robust solution that ensures accuracy while streamlining workflows. This approach is particularly valuable in professional settings where data integrity cannot be compromised.