EXCEL数据有效性前置校验的两种方式

背景：
生活中经常用excel作为模板供用户批量录入数据，然后在上传系统，等到系统校验有问题后，用户又要下载进行修改重新上传，这样比较麻烦，所以如何把问题数据的发现阶段前置到用户录入的阶段，那么有问题立即提醒立即修改，用户录入数据的效率也会大大改善，用户的体验也会好很多。基于这种常见的生活场景，如何在用户录入数据时就校验数据有效性呢？这里总结两种方式如下：

一、通过EXCEL数据验证功能保证数据的有效性

第一步：依次点击【数据】-【数据验证】-在弹出数据验证设置框点击【设置】进行设置选中的区域的数据格式。

第二步：在数据验证设置框上点击出错警告可以设置数据校验出错的时候提示标题及内容

第三步：验证一下，故意输入错误的数据类型的数据

二、通过VBA编程，嵌入VBA脚本进行验证。

excel中有不同的事件触发机制，只要把校验程序嵌入到这些触发事件中就可以了比如：
1、可以在单元格输入后验证

Private Sub Worksheet_Change(ByVal Target As Range)
    If not IsNumeric(Target)  Then 
    MsgBox("请输入数字")
End Sub

2、可以在关闭Excel时验证

Private Sub Workbook_BeforeClose(Cancel As Boolean)
     '这里写调用验证程序
     '验证不通过设置Cancel  为true
End Sub

列如我这里校验Sheet1中的数据 写了一个checkData函数

Sub checkData()
    Dim offsetSheet As Worksheet
    Set Worksheet = ThisWorkbook.Sheets("Sheet1") '获取Sheet1
    
    Dim row_d As Integer, column_d As Integer
    
    'column_d = Worksheet.Range("IV1").End(xlToLeft).Column '从255往左获取最后一列不为空的列号
    column_d = 6 '因为是模板列数是固定的，这里直接写死
    
    row_d = Worksheet.Range("A65536").End(xlUp).Row '从65536往上获取最后一行不为空的行号
    
    '如果第一行都为空，说明模板被修改，直接提示
    If row_d = 1 Then
        MsgBox ("模板首行是标题行不可以修改")
        Exit Sub
    End If
    
    
    Dim text_trim As String, text As String
    Dim validResult As String
    Dim i%
    Dim j%
    
    For j = 1 To column_d
        For i = 2 To row_d
        
            If j < 3 Then
                If Not Application.isText(Worksheet.Cells(i, j)) Then
                
                    validResult = concatResult(validResult, i, j, "不是文本类型")
                    
                End If
            
            ElseIf j = 3 Then
                If Not IsDate(Worksheet.Cells(i, j)) Then
                
                    validResult = concatResult(validResult, i, j, "不是日期类型")
                    
                End If
            ElseIf j < 6 Then
            
                If Not IsNumeric(Worksheet.Cells(i, j)) Then
                    validResult = concatResult(validResult, i, j, "不是数字")
                End If
            ElseIf j = 6 Then
            
            End If
        Next i
    Next j
    
    If validResult = "" Then
        MsgBox ("校验成功")
    Else
        MsgBox (validResult + "请修改")
    End If
End Sub

'数字列号转字母
Function CNtoW(ByVal num As Long) As String
CNtoW = Replace(Cells(1, num).Address(False, False), "1", "")
End Function

'字母列号转数字
Function CWtoN(ByVal AB As String) As Long
CWtoN = Range("A1:" & AB & "1").Cells.Count
End Function

'拼接校验结果
Function concatResult(ByVal validResult As String, ByVal rowNum As Long, ByVal colNum As Long, ByVal newResult As String) As String

    If validResult <> "" Then
        validResult = validResult & " , " & CNtoW(colNum) & rowNum & newResult
    Else
        validResult = CNtoW(colNum) & rowNum & newResult
    End If
    concatResult = validResult
End Function

然后在关闭工作薄事件中进行引用执行检查

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim validResult As String
    validResult = Sheets("Sheet1").checkData
    If validResult <> "" Then
        Cancel = True
    End If
End Sub