VBA实现单元格DataValidation双精度值二选一的问题求助

原代码问题分析 #

你遇到的4个选项问题，核心原因是区域设置的小数分隔符干扰：当Double类型数值转为字符串时，如果系统默认用逗号作为小数分隔符，-0.47762776914086会被自动转为-0,47762776914086，拼接后的公式字符串会包含多个逗号，Excel会把每个逗号都当作列表项的分隔符，最终拆成-0、47762776914086、-1、15182691509465这4个选项。

修正方案 #

方案1：强制统一分隔规则，避免区域干扰 #

通过Str()函数或格式化转换，确保数值转字符串时用点作为小数分隔符，再用英文逗号分隔两个列表项：

Sub ListeDeroulante()
    Dim ws As Worksheet
    Dim values1 As Double
    Dim values2 As Double
    Dim rng As Range
    Dim validationFormula As String
    Set ws = ThisWorkbook.Worksheets("Feuil1")
    Set rng = ws.Range("B198")
    values1 = -0.47762776914086
    values2 = -1.15182691509465

    rng.Validation.Delete

    ' 用Str()强制转换为带点分隔的字符串，再用英文逗号拼接
    validationFormula = Str(values1) & "," & Str(values2)
    ' 也可以用Format指定精度：validationFormula = Format(values1, "0.00000000000000") & "," & Format(values2, "0.00000000000000")

    With rng.Validation
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=validationFormula
        .IgnoreBlank = True
        .InCellDropdown = True
    End With
End Sub

方案2：直接传递数组（最可靠，彻底规避分隔符问题） #

数据验证的Formula1支持直接接收数组，无需拼接字符串，完全不受区域设置影响：

Sub ListeDeroulante()
    Dim ws As Worksheet
    Dim valuesArr As Variant
    Dim rng As Range
    Set ws = ThisWorkbook.Worksheets("Feuil1")
    Set rng = ws.Range("B198")
    ' 直接定义包含目标数值的数组
    valuesArr = Array(-0.47762776914086, -1.15182691509465)

    rng.Validation.Delete

    With rng.Validation
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=valuesArr
        .IgnoreBlank = True
        .InCellDropdown = True
    End With
End Sub

批量为表格单元格设置专属数据验证 #

如果要给表格每个单元格配置对应的两个值，可提前把对应值存到表格的关联列（比如同一行的C、D列），再遍历设置：

Sub BatchValidationForTable()
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim cell As Range
    Dim rowNum As Integer
    Dim val1 As Double, val2 As Double
    Dim valuesArr As Variant

    Set ws = ThisWorkbook.Worksheets("Feuil1")
    Set tbl = ws.ListObjects("Table1") ' 替换为你的表格名称

    For Each cell In tbl.DataBodyRange.Columns(2).Cells ' 假设要设置的是表格第2列
        rowNum = cell.Row - tbl.HeaderRowRange.Row
        ' 从同一行的C、D列获取对应两个值（可根据实际位置调整）
        val1 = tbl.DataBodyRange(rowNum, 3).Value
        val2 = tbl.DataBodyRange(rowNum, 4).Value
        valuesArr = Array(val1, val2)

        cell.Validation.Delete
        With cell.Validation
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=valuesArr
            .IgnoreBlank = True
            .InCellDropdown = True
        End With
    Next cell
End Sub

内容的提问来源于stack exchange，提问作者EOST