数据验证(在早期Excel版本中称为数据有效性)是Excel中的常用功能,使用此功能可以确保数据的规范化,如下图A列(序列:张三,李四)和C列(序列:早班,晚班)设置了数据验证。
依次单击【数据验证】>【圈释无效数据】,无效数据将被标注出来,如下图所示。
值得注意的是:圈释无效数据添加的椭圆并不是普通的图形(Shape对象),因此无法使用VBA直接读取无效数据所在单元格区域。
山穷水尽疑无路,柳暗花明又一村,此路不通,肯定还有其他的方法。VBA中还有其他方法来识别无效数据。
Sub Demo()
Dim res As Range, c As Range, rngVal As Range
Set rngVal = [a1].CurrentRegion.SpecialCells(xlCellTypeAllValidation)
If Not rngVal Is Nothing Then
For Each c In rngVal
If Not c.Validation.Value Then
If res Is Nothing Then
Set res = c
Else
Set res = Union(res, c)
End If
End If
Next
If Not res Is Nothing Then MsgBox "无效数据:" & res.Address(0, 0)
Else
MsgBox "没有设置数据验证"
End If
End Sub
【代码解析】
第3行代码使用SpecialCells(xlCellTypeAllValidation)
获取设置数据验证的单元格区域。
第4行代码判断工作表是否已经设置数据验证,如果没有,则第16行代码给出提示,退出程序运行。
第5~13代码循环处理每个单元格。
第6行代码使用Range
对象的Validation
属性进行判断,无效数据单元格返回值为False。
第7~11代码将无效数据单元格赋值给对象变量res
。
第14行代码输出结果如下。