Excel工作表如何使用VBA添加数据有效性

使用Excel过程中，经常通过人工设置数据有效性来限制目标单元格只能接受某种条件的参数。如何使用VBA来给工作表添加数据有效性呢，这里水文工具集从网上摘录一篇通过VBA给任意工作表设置数据有效性的文章(Add Data Validation to any worksheet using VBA)。

首先定义VBA函数AddValidation，具体源代码如下：

Sub AddValidation(targetRange As Excel.Range, _
 validationType As XlDVType, _
 AlertStyle As XlDVAlertStyle, _
 Optional shwErr As Boolean = True, _
 Optional errMsg As String, _
 Optional errTitle As String, _
 Optional shwinp As Boolean = False, _
 Optional inpmsg As String, _
 Optional inptitle As String, _
 Optional igblank As Boolean = True, _
 Optional dropdown As Boolean = True, _
 Optional Operator As XlFormatConditionOperator, _
 Optional Formula1 As Variant, _
 Optional Formula2 As Variant)
 
    Dim currentValidation As Excel.Validation
    Dim op As XlFormatConditionOperator
 
    ' 捕获Validation对象
    Set currentValidation = targetRange.Validation
 
    ' 如果数据有效性类型是"序列"或"自定义", 操作符必须是"介于"
    If (validationType = xlValidateList Or validationType = xlValidateCustom) Then
        op = xlBetween
    Else
        op = Operator
    End If
 
    ' 如果操作符是"介于" 或 "未介于" (不是序列或自定义),
    ' 那么必须指定Formula2
    If (op = xlNotBetween Or op = xlBetween) Then
        If (validationType <> xlValidateList And validationType <> xlValidateCustom) Then
            If IsMissing(Formula2) Then
                MsgBox "如果操作符是'介于'或者'未介于'并且类型不是'序列'或'自定义'," & _
                    "那么必须指定Formula2."
                Exit Sub
            End If
        End If
    End If
 
    ' 如果已经存在数据有效性检查对象,则移除
    currentValidation.Delete
 
    ' 创建新的数据有效性检查对象
    With currentValidation
        .Add validationType, AlertStyle, op, Formula1, Formula2
 
        ' 设置错误提示消息
        .ShowError = shwErr
        .ErrorMessage = IIf(shwErr, errMsg, "")
        .ErrorTitle = IIf(shwErr, errTitle, "")
 
        ' 设置输出消息
        .ShowInput = shwinp
        .InputMessage = IIf(shwinp, inpmsg, "")
        .InputTitle = IIf(shwinp, inptitle, "")

        ' 是否忽略空单元格
        .IgnoreBlank = igblank
        .InCellDropdown = dropdown
    End With
 
End Sub

上述VBA数据有效性设置过程AddValidation接受下列参数：

• 添加数据有效性的目标单元格区域
• 数据有效性设置的有效性类型(一个内置常量)
• 警告样式(另一个内置常量)
• 是否希望显示错误信息框
• 错误消息文本
• 错误消息框标题
• 是否在输入数据有效性单元格时显示消息框
• 输出消息文本
• 输出消息框的标题
• 是否忽略空单元格
• 是否在选择数据有效性单元格时显示下拉箭头
• 使用的操作符(另一个内置常量)
• 最后是两个变量，包含希望是有效性一部分的公式或值

这个程序首先捕获相应的Range.Validation对象，然后检查有效性类型。如果是“序列”或“自定义”，操作符参数必须是“介于”。可以在数据有效性对话框中查看，当选择类型时，如果是“序列”或“自定义”，操作符框中变为“介于”并且不能修改。

如果有效性类型不是“序列”或“自定义”，并且操作符是“介于”或“未介于”，那么必须指定参数Formula2(因为这是唯一需要值区域的两个操作符)。

一旦检查完毕，就清除已存在的有效性(currentValidation.Delete)。然后在调用该过程时使用指定的参数添加新的Validation对象。

使用示例：

Sub TestAddValidation()
    Dim rng As Excel.Range
 
    Set rng = Range("A1:A100")
 
    'My_Data_Range为定义的名称
    AddValidation rng, xlValidateList, _
        xlValidAlertStop, , "不可以输入无效数字!", "无效数字", , , , , , xlBetween, "=My_Data_Range"
End Sub