我之前帮客户解决过一模一样的问题——你遇到的情况本质是直接用逗号拼接字符串作为数据验证的Formula1，在Excel 2010里很容易触发兼容性问题：

1. Excel 2010对数据验证的Formula1字符串长度有严格限制（最多255字符），一旦选项多或者包含特殊字符，就会生成不符合XML规范的内容；
2. 直接写入字符串的方式，在保存时Excel的XML序列化过程中容易出现格式错误，导致重新打开时被判定为“不可读取部分”。

最优解决方案：用单元格引用代替字符串拼接 #

把下拉选项存到一个隐藏的工作表区域，再让数据验证引用这个区域，这样Excel能正确解析并保存，彻底解决报错问题。

修改后的AddLookupToRange代码如下：

Sub AddLookupToRange(f As tField, r As Range)
    If Not f.IsLookup Then Exit Sub
    
    Dim lookupSheet As Worksheet
    Dim targetRange As Range
    Dim k As Long
    
    ' 获取或创建专门存下拉选项的隐藏工作表
    On Error Resume Next
    Set lookupSheet = ThisWorkbook.Worksheets("LookupData")
    On Error GoTo 0
    
    If lookupSheet Is Nothing Then
        Set lookupSheet = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
        lookupSheet.Name = "LookupData"
        lookupSheet.Visible = xlSheetVeryHidden ' 彻底隐藏，仅VBA可访问
    End If
    
    ' 定位当前字段的存储起始行（用第一列标记字段名，方便后续管理）
    Dim fieldRow As Long
    fieldRow = lookupSheet.Cells(lookupSheet.Rows.Count, 1).End(xlUp).Row + 1
    lookupSheet.Cells(fieldRow, 1).Value = f.Name
    
    ' 写入所有下拉选项
    For k = 0 To f.Lookup.Elements.Count - 1
        lookupSheet.Cells(fieldRow + k, 2).Value = f.Lookup.Elements.Items(k)
    Next k
    
    ' 定义选项的引用范围
    Set targetRange = lookupSheet.Range(lookupSheet.Cells(fieldRow, 2), lookupSheet.Cells(fieldRow + f.Lookup.Elements.Count - 1, 2))
    
    ' 设置数据验证
    With r.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlEqual, _
             Formula1:="=" & targetRange.Address(External:=True)
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub

额外注意事项 #

• 隐藏工作表用xlSheetVeryHidden，避免用户误操作删除；如果需要调试，可以用VBA代码ThisWorkbook.Worksheets("LookupData").Visible = xlSheetVisible临时显示；
• 如果你的字段数量较多，建议定期清理LookupData工作表中的旧数据，避免冗余；
• 这种方式不仅解决了兼容性问题，还支持更多数量的下拉选项（没有255字符限制），后续修改选项也更方便，直接修改隐藏工作表的内容即可。

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