Excel VBA写入数据时触发原有数据验证规则并校验回滚的实现方法

VBA直接给单元格赋值时Excel默认会跳过数据验证的触发逻辑，你可以直接调用单元格自带的Validation原生校验接口复用已配置的规则，不需要重新编写验证逻辑，修改后的代码如下：

Private Sub save_data()
    Dim objList As ListObject
    Dim form As Worksheet
    Dim newRow As ListRow
    Dim validateCell As Range
    
    Set objList = ThisWorkbook.Sheets("Sheet 2").ListObjects("Table1")
    Set form = ThisWorkbook.Sheets("Sheet 1")
    Set newRow = objList.ListRows.Add(AlwaysInsert:=True)
    
    On Error GoTo ErrHandler ' 捕获校验失败的错误
    Application.EnableEvents = True
    
    ' 写入数据后执行校验
    With newRow.Range
        .Cells(1, 1).Value = form.Range("C2").Value
        ' 有其他列需要写入可以在此处补充赋值逻辑
        
        ' 遍历当前新增行所有单元格，调用已有验证规则校验
        For Each validateCell In .Cells
            ' 跳过没有配置数据验证的单元格
            If validateCell.Validation.Type <> xlValidateInputOnly Then
                validateCell.Validation.Validate ' 校验失败会自动抛出错误
            End If
        Next validateCell
    End With
    
    ' 全部校验通过流程
    MsgBox "数据保存成功", vbInformation
    Exit Sub

ErrHandler:
    ' 校验失败回滚：删除本次新增的行
    If Not newRow Is Nothing Then
        Application.DisplayAlerts = False
        newRow.Delete
        Application.DisplayAlerts = True
    End If
    ' 弹出验证规则配置的错误提示
    MsgBox "数据保存失败：" & Err.Description, vbExclamation
End Sub

关键逻辑说明 #

• Range.Validation.Validate是Excel原生提供的校验方法，会直接复用单元格已经设置的所有数据验证规则、错误提示内容，不需要二次开发
• 错误捕获分支会在校验失败时自动删除本次新增的行，不会在Sheet2中残留无效数据
• 兼容无验证规则的单元格，不会触发无意义的报错

内容的提问来源于stack exchange，提问作者Veki