Excel类型检查

            平常在做数据导入的时候，经常会遇到输入错误的数据导致导入数据失败，比如，员工名字数据库长度为5，在数据导入的时候，如果长度超过5，数据库导入就会失败，又如，明明是int类型的数据，结果不小心写成string类型，那么在导入之前能不能做一个检查呢？在程序中是可以的，但是在程序中检查会给人带来使用不便的体验，没有人愿意在做完一堆数据以后发现导不进去然后又转回去修改，那么在一开始做数据的时候就检查数据合法性理所当然是最好的。 用Excel打开附件，使用alt+F11可以查看源代码

下面是实现代码：

Private Sub CommandButton1_Click()
    'Dim Name As String
    Dim rows As Integer
    Dim myRange As Range
    Dim columns As Integer
    Dim tempnum As Integer
    Dim length As Integer
    Dim chickRsult As Boolean
   
    'Name = Worksheets("Sheet2").Cells(3, 2).Value
    '取得实际行数
    rows = ActiveSheet.UsedRange.rows.Count
    '取得实际列数
    columns = ActiveSheet.UsedRange.columns.Count
    '初始化
    Set myRange = Worksheets("Sheet2").Range(Cells(1, 1), Cells(rows, columns))
    chickRsutlt = False
    myRange.Select
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    
    'chick 开始
    For n = 2 To columns
        
        'String类型chick 长度chick
        If Worksheets("Sheet2").Cells(num, n).Value = typeString Then
        For i = num + 2 To rows
            If Len(Worksheets("Sheet2").Cells(i, n).Value) > Worksheets("Sheet2").Cells(num + 1, n).Value Then
               Worksheets("Sheet2").Cells(i, n).Select
                With Selection.Interior
                    .Pattern = xlSolid
                    .PatternColorIndex = xlAutomatic
                    .Color = 255
                    .TintAndShade = 0
                    .PatternTintAndShade = 0
                End With
                chickRsult = True
            End If
        Next
        'int 类型chick 是否是整数，长度，是否为负数
        ElseIf Worksheets("Sheet2").Cells(num, n).Value = typeInt Then
        For i = num + 2 To rows
            If IsNumeric(Worksheets("Sheet2").Cells(i, n).Value) <> True Then
                Worksheets("Sheet2").Cells(i, n).Select
                    With Selection.Interior
                        .Pattern = xlSolid
                        .PatternColorIndex = xlAutomatic
                        .Color = 255
                        .TintAndShade = 0
                        .PatternTintAndShade = 0
                    End With
                    chickRsult = True
            Else
                tempnum = Int(Worksheets("Sheet2").Cells(i, n).Value)
                If Not (tempnum > 0 And VarType(tempnum) = vbInteger And Len(Worksheets("Sheet2").Cells(i, n).Value) <= Worksheets("Sheet2").Cells(num + 1, n).Value) Then
                    Worksheets("Sheet2").Cells(i, n).Select
                        With Selection.Interior
                            .Pattern = xlSolid
                            .PatternColorIndex = xlAutomatic
                            .Color = 255
                            .TintAndShade = 0
                            .PatternTintAndShade = 0
                        End With
                        chickRsult = True
                End If
            End If
        Next
        'date 类型chick
        ElseIf Worksheets("Sheet2").Cells(num, n).Value = typeDate Then
        For i = num + 2 To rows
            If VBA.IsDate(Worksheets("Sheet2").Cells(i, n).Value) = False Then
               Worksheets("Sheet2").Cells(i, n).Select
                With Selection.Interior
                    .Pattern = xlSolid
                    .PatternColorIndex = xlAutomatic
                    .Color = 255
                    .TintAndShade = 0
                    .PatternTintAndShade = 0
                End With
                chickRsult = True
            End If
        Next
        End If
  Next
  If chickRsult Then
  MsgBox ("输入值类型错误，请检查")
  End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub