Excel揭秘25：突破数据有效性列表的字符限制

数据有效性（Excel2013版之后称为“数据验证”）是一个很有用的功能，也是用户的常用功能之一，特别是使用数据有效性列表。如下图1所示，在“数据验证”对话框中，选择“允许”下拉列表中的“序列”，在“来源”框中设置数据列表来源。

图1

数据有效性列表的数据来源有两种设置方式：

1. 使用逗号分隔的字符串

2. 使用单元格区域

然而，如果使用逗号分隔的字符串作为数据列表的来源，你会发现这样的字符串的字符数被限制为255个字符，超过此限制的字符串根本无法输入到数据来源中。当然，这样的限制不会带来问题，因为你还可以使用单元格区域中的数据作为数据列表来源。

但是，有趣的是，使用VBA代码编程，却也可以给数据列表提供大于255个字符的字符串。

下面的代码创建了一个逗号分隔的344个字符的字符串，并在A1中创建了一个数据有效性列表。

Sub foo()

Dim strArrValidation(0 To 70) As String

Dim strValidation As String

Dim i As Long

For i =LBound(strArrValidation) To UBound(strArrValidation)

strArrValidation(i) = "项目" & CStr(i)

Next i

strValidation = Join$(strArrValidation, ",")

Debug.Print Len(strValidation) '344个字符

With Range("A1").Validation

.Delete

.Add Type:=xlValidateList, Formula1:=strValidation

End With

End Sub

运行代码后的结果如下图2所示。

图2

可以看到，下拉列表项从“项目0”至“项目70”，共有344个字符。

下面，让我们核查一下这个超长的列表是否被保存了。将工作簿保存为.xlsm文件后，关闭该工作簿。然后，将其扩展名更改为.zip，接着打开这个压缩文件，导航到xl文件夹，打开数据有效性所在的工作表，示例中是sheet2.xml，打开它，你可以看到formula1元素下有71个项已被保存到文件中，如下图3所示。

图3

关闭打开的压缩文件夹，将其扩展名改回.xlsm，然后在Excel中打开，你会发现Excel弹出下图4所示的提示信息。

图4

如果单击“是”，选择修复，将会弹出下图5所示的提示信息。

图5

在打开的工作簿中，数据有效性列表已被删除。

复修记录并没有告诉我们数据有效性列表被移除的原因，但可以肯定的是，列表字符数超出了字符数限制。

你可能会说，我可以在Workbook_Open事件中添加长列表，然后在Workbook_BeforeSave事件中将其删除以避免修复问题，但是使用代码绕过限制字符数没有很好的用处。

注：本文学习整理自colinlegg.wordpress.com，供有兴趣的朋友参考。