Excel VBA - 验证数据和逗号分隔的字符串

Question

我正在尝试纯粹使用 VBA 创建数据验证单元。问题是，如果其中之一
验证列表中的项目是包含逗号的 Excel 公式(例如以下代码中的 IF())，则会生成错误。

 Dim str As String
 str = "=IF(SUM(A1:A2) = 0, ""Zero"", SUM(A1:A2)) , Item 2 , Item 3 , Item 4"

 s1.Range("B6").Validation.Add Type:=xlValidateList, _
          AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=str

生成错误(运行时错误 1004)是因为它在第一个逗号处分隔字符串，因此验证的第一项是 "=IF(SUM(A1:A2) = 0" .我希望逗号有转义字符(例如 C 中的\)来避免这种情况。
我希望经过验证的列表如下所示:

<result of IF() function>
Item 2
Item 3
Item 4

注意:我不能对已验证的数据使用范围。如果用户删除了该范围，则电子表格将被破坏。

Answer 1

当单元格 A1 或 A2 更改时，如何使用 WorkSheet_Change 事件重置数据验证？

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim ValidationString As String
Dim rngChanging As Excel.Range
Dim rngWithValidation As Excel.Range

Set rngChanging = Range("A1:A2")
Set rngWithValidation = Range("B6")
If Not Intersect(Target, rngChanging) Is Nothing Then
    With rngWithValidation
        .Validation.Delete
        If WorksheetFunction.Sum(rngChanging) = 0 Then
            ValidationString = "0"
        Else
            ValidationString = WorksheetFunction.Sum(rngChanging)
        End If
        ValidationString = ValidationString & ",Item2,Item3,Item4"
        .Validation.Add Type:=xlValidateList, Formula1:=ValidationString
    End With
End If
End Sub