|
平常在做数据导入的时候,经常会遇到输入错误的数据导致导入数据失败,比如,员工名字数据库长度为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