如何通过代码实现含多公式的Excel Data Validation列表？

要实现通过代码而非硬编码的方式，给G5单元格设置包含指定两个公式的数据验证列表，可通过以下VBA代码完成：

核心设置代码（标准模块） #

Sub SetDynamicDataValidation()
    Dim targetCell As Range
    Dim validationOptions As String
    
    ' 定位目标单元格G5
    Set targetCell = ThisWorkbook.ActiveSheet.Range("G5")
    
    ' 拼接数据验证的两个公式选项，用逗号分隔；公式内的双引号需用双引号转义
    validationOptions = "=CONCATENATE(A1,B1),=CONCATENATE(A1,B1,"" - "",D$1$)"
    
    ' 清除单元格原有数据验证，避免冲突
    targetCell.Validation.Delete
    
    ' 配置新的数据验证规则
    With targetCell.Validation
        .Add Type:=xlValidateList, _
             AlertStyle:=xlValidAlertStop, _
             Formula1:=validationOptions
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = True
        .ShowError = True
    End With
    
    ' 可选：给G5设置初始公式
    targetCell.Formula = "=CONCATENATE(A1,B1)"
End Sub

自动转换公式的触发代码（工作表模块） #

上述代码设置的数据验证列表会显示公式文本，若需要选中选项后自动将文本转换为可执行的公式，需在G5所在工作表的代码模块中添加以下事件代码：

Private Sub Worksheet_Change(ByVal Target As Range)
    ' 仅当G5单元格内容变化时触发
    If Not Intersect(Target, Me.Range("G5")) Is Nothing Then
        Application.EnableEvents = False ' 防止循环触发
        ' 检查内容是否为公式格式，若是则转为实际公式
        If Left(Target.Value, 1) = "=" Then
            Target.Formula = Target.Value
        End If
        Application.EnableEvents = True
    End If
End Sub

关键说明 #

1. 数据验证的列表选项需用逗号分隔，公式内的双引号在VBA中必须用两个双引号转义（如"" - ""）
2. 先清除原有数据验证规则，可避免因重复添加导致的运行错误
3. 工作表事件代码需放在对应工作表的代码窗口中（右键工作表标签→查看代码），而非标准模块

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