Excel中的最大下拉列表/公式长度 [英] Maximum drop-down list/formula length in Excel
问题描述
我有以下VBA代码:
myList = "test"
Range("A1:Z1").Validation.Delete
Range("A1:Z1").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=myList
如果我的列表超过256个字符,则会被截断.如果超过1024,则会出现执行错误(我认为这是公式的最大长度).
If my list exceeds 256 characters, it is truncated. If it exceeds 1024, I get an execution error (I think it is the maximum length for a formula).
我如何克服这些限制?我正在使用Excel 2003.
How can I get over those limits? I'm using Excel 2003.
推荐答案
使用上面评论中发布的链接,我可以创建一个包含超过256/1024个字符的下拉列表.经过多次搜索和尝试后,我设法获得了以下信息:
Using the link posted in the comments above, I was able to create a dropdown list with more than 256/1024 chars. Here's what I managed to get after some more searches and tries:
ActiveWorkbook.Names.Add Name:="List", RefersTo:="=ValidationList!$A$1:$A$" & i
Range("A1:AZ1").Validation.Delete
Range("A1:AZ1").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=List"
仅此而已. i
是 ValidationList 工作表的行数.我正在动态创建该工作表,并使用要显示在下拉列表中的内容填充第一列A.
Nothing more than this. i
is the number of lines of ValidationList sheet. I'm creating that sheet dynamically and populating the first A column with what I want to show up in the dropdown list.
有用的参考文献:
- http://www.contextures.com/xlDataVal11.html
- http://www.ozgrid.com/forum/showthread.php?t = 52916
- http://msdn.microsoft.com/en-us/library/office/ff839847.aspx
- >://social.msdn.microsoft.com/Forums/zh-CN/isvvba/thread/830b42cf-8c97-4aaf-b34b-d860773281f7/
- VBA excel代码:无法将命名范围指定为验证中的一级方程式
这篇关于Excel中的最大下拉列表/公式长度的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!