VBA Data Validation: Prevent Errors

Nothing is worse than finding out your data is incorrect 5 minutes before a meeting. By the end of this post, you'll be able to create custom data validation rules using VBA to prevent errors in your Excel spreadsheets. Imagine you have a dataset of 5,000 customer records, and you need to ensure that the email addresses are valid.

Setting Up Data Validation with VBA

Imagine you have a dataset of customer information, and you want to ensure that the phone numbers are in the correct format. You can use VBA to create a custom data validation rule.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 2 Then
        If Not IsValidPhoneNumber(Target.Value) Then
            MsgBox "Invalid phone number"
            Target.ClearContents
        End If
    End If
End Sub

Function IsValidPhoneNumber(phoneNumber As String) As Boolean
    Dim regex As Object
    Set regex = CreateObject("VBScript.RegExp")
    regex.Pattern = "^\d{3}-\d{3}-\d{4}$"
    IsValidPhoneNumber = regex.Test(phoneNumber)
End Function

Common Mistakes

• Forgetting to set the Target range correctly
• Not handling errors properly using Try-Catch blocks
• Using the wrong regular expression pattern

Real-World Example

Suppose you have a dataset of employee information, and you want to ensure that the employee IDs are unique. You can use VBA to create a custom data validation rule.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 1 Then
        If Not IsUniqueEmployeeID(Target.Value) Then
            MsgBox "Employee ID already exists"
            Target.ClearContents
        End If
    End If
End Sub

Function IsUniqueEmployeeID(employeeID As String) As Boolean
    Dim lastRow As Long
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    For i = 1 To lastRow
        If Cells(i, 1).Value = employeeID Then
            IsUniqueEmployeeID = False
            Exit Function
        End If
    Next i
    IsUniqueEmployeeID = True
End Function

You can use the VLOOKUP function to check if the employee ID already exists in the dataset.

• Error 1004: "Method 'Range' of object '_Worksheet' failed" - This error occurs when the Target range is not set correctly. Check that the Target range is set to the correct column and row.
• Error 13: "Type mismatch" - This error occurs when the data type of the value being validated does not match the expected data type. Check that the data type of the value being validated is correct.
• Error 76: "Path not found" - This error occurs when the file path being used is incorrect. Check that the file path is correct and that the file exists.

Stop fighting with syntax. Generate this formula instantly with our tool. Use the Excel Formula Generator