Automating data quality checks in Excel using VBA

Automating data quality checks in Excel using VBA (Visual Basic for Applications) can be very useful for ensuring the integrity and reliability of your data. Below is an example of detailed VBA code to perform several common data quality checks such as:

1. Checking for duplicates in a column.
2. Checking for missing values (empty cells).
3. Checking if values conform to a specific format (e.g., dates or numbers).
4. Checking if values are within a specific range (e.g., scores should not be below 0 or above 100).

Example VBA Code for Automating Data Quality Checks

Sub DataQualityControl()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim col As Long
    Dim cell As Range
    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")
    ' Set the worksheet to be analyzed
    Set ws = ThisWorkbook.Sheets("Sheet1")   
    ' Find the last used row in column A (adjust this as per the column being used)
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row   
    ' 1. Check for duplicates in column A
    MsgBox "Checking for duplicates in column A..."
    For i = 2 To lastRow ' Assuming data starts from row 2
        If dict.exists(ws.Cells(i, 1).Value) Then
            ws.Cells(i, 1).Interior.Color = RGB(255, 0, 0) ' Highlight duplicates in red
        Else
            dict.Add ws.Cells(i, 1).Value, Nothing
        End If
    Next i
    ' 2. Check for missing values in column B
    MsgBox "Checking for missing values in column B..."
    For i = 2 To lastRow
        If IsEmpty(ws.Cells(i, 2).Value) Then
            ws.Cells(i, 2).Interior.Color = RGB(255, 255, 0) ' Highlight missing values in yellow
        End If
    Next i
    ' 3. Check for date format in column C
    MsgBox "Checking for date format in column C..."
    For i = 2 To lastRow
        If Not IsDate(ws.Cells(i, 3).Value) And ws.Cells(i, 3).Value <> "" Then
            ws.Cells(i, 3).Interior.Color = RGB(255, 165, 0) ' Highlight invalid date format in orange
        End If
    Next i
    ' 4. Check for range of values in column D (e.g., scores between 0 and 100)
    MsgBox "Checking for valid range in column D..."
    For i = 2 To lastRow
        If IsNumeric(ws.Cells(i, 4).Value) Then
            If ws.Cells(i, 4).Value < 0 Or ws.Cells(i, 4).Value > 100 Then
                ws.Cells(i, 4).Interior.Color = RGB(0, 255, 0) ' Highlight out-of-range values in green
            End If
        End If
    Next i   
    MsgBox "Data quality checks are complete."
End Sub

Explanation of the Code

1. Initialization and Worksheet Setup:
   • The first step is to define the worksheet to be analyzed (in this case, « Sheet1 »).
   • The lastRow variable is used to find the last used row in column A to loop through the data.
2. Duplicate Check in Column A:
   • A Scripting.Dictionary is used to track unique values in column A.
   • If a value appears more than once, it’s considered a duplicate, and the corresponding cell is highlighted in red.
3. Missing Values Check (Empty Cells) in Column B:
   • The code loops through each cell in column B and checks if the cell is empty (IsEmpty).
   • If a cell is empty, it’s highlighted in yellow.
4. Date Format Check in Column C:
   • The code checks whether the values in column C are valid dates using the IsDate function.
   • If a cell contains a value that is not a valid date, it is highlighted in orange.
5. Range Check in Column D:
   • The code checks if the values in column D are numeric and whether they fall within the range 0 to 100.
   • If the value is out of range, the corresponding cell is highlighted in green.
6. Message Boxes:
   • Message boxes are shown before each check to inform the user of the ongoing quality checks.

Possible Improvements

• Customization: You can customize this code based on your data’s specific requirements. For instance, you can add additional checks for other types of data quality issues.
• Summary Report: It might be useful to generate a summary report at the end of the checks, detailing the errors or issues found in the data.

How to Use This Code

1. Open Excel.
2. Press Alt + F11 to open the VBA editor.
3. In the VBA editor, click on Insert > Module.
4. Copy and paste the code above into the module.
5. Close the VBA editor.
6. To run the code, go to the « Developer » tab in Excel, click on « Macros, » select DataQualityControl, and click « Run. »

This code serves as a starting point for automating data quality checks in Excel using VBA. You can modify it further to suit your specific needs.