```shell
Option Explicit
 
Sub sfz()
    Dim i As Integer
    Dim rng1 As range, rng2 As range
    Dim sh As Worksheet
    Dim total As Integer
 
    Set sh = Sheets(1)
    total = Application.WorksheetFunction.CountA(sh.range("A:A"))
    
    For i = 2 To total
        Set rng1 = sh.range("A" & i)
        Set rng2 = sh.range("B" & i)
        
        If Not is18(rng1) Then
            rng2 = "错误，位数不对"
        Else
            If Not isNum17(rng1) Then
                rng2 = "错误，出现字母"
            Else
                If Not ymd(rng1) Then
                    rng2 = "日期不对"
                    Else
                        If jiaoyan(rng1) = 0 Then
                            rng2 = "出现错误，请复核"
                        Else
                            rng2 = "正确"
                        End If
                    End If
            End If
        End If
    Next
End Sub
 
Private Function is18(rng As range) As Boolean
    '判断身份证是否是18位
    is18 = True
    If Len(rng.Value) <> 18 Then
        is18 = False
    End If
End Function
 
Private Function isNum17(rng As range) As Boolean
    '判断1-17位是否是数字
    isNum17 = True
    Dim i As Integer
    For i = 1 To 17
        If Not IsNumeric(Mid(rng, i, 1)) Then
            isNum17 = False
        End If
    Next
End Function
 
Private Function ymd(rng As range) As Boolean
    ymd = True
    '对年月日进行判断，年份不管，月份数字小于12即可，主要是日期
    Dim days As String
    days = "31,28,31,30,31,30,31,31,30,31,30,31"
    Dim mon As Integer
    Dim day As Integer
    Dim year As Integer
    
    year = Mid(rng, 7, 4)
    mon = Mid(rng, 11, 2)
    day = Mid(rng, 13, 2)
    
    '月份位置大于12返回false
    If mon > 12 Then
        ymd = False
    Else
    '日期出现错误返回false
        If day > Split(days, ",")(mon - 1) Then
            ymd = False
        End If
        
        '闰年的再单独拎出来计算一下
        '一句太长还不好理解，我就写成两个判断语句了
        If (year Mod 400 = 0) And (day <= 29) Then
            ymd = True
        End If
        If (year Mod 4 = 0) And (year Mod 100 <> 0) And (day <= 29) Then
            ymd = True
        End If
    End If
    
End Function
 
 
Private Function jiaoyan(rng As range) As Boolean
    '计算1-17位的加权和
    Dim i As Integer
    Dim sum
    sum = 0
    '定义身份证号1-17位加权的系数，并计算加权总和
    Dim xishu As String
    xishu = "7,9,10,5,8,4,2,1,6,3,7,9,10,5,8,4,2"
    
    For i = 1 To 17
        sum = sum + Val(Mid(rng, i, 1)) * Val(Split(xishu, ",")(i - 1))
    Next
 
    
    '定义身份证号1-17位加权后除以11的余数和身份证最后一位进行判断
    Dim yushu As String
    Dim ys As String
    yushu = "1,0,X,9,8,7,6,5,4,3,2"
    
    ys = (Split(yushu, ",")(sum Mod 11))
            
    If ys <> Right(rng, 1) Then
        jiaoyan = False
    Else
        jiaoyan = True
    End If
End Function
spam fails_task lang_mismatch pii not_appropriate hate_speech sexual_content quality toxicity humor helpfulness creativity violence