excelperfect
数据有效性（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，供有兴趣的朋友参考。
联系客服
