从大型数组创建的Excel验证列表 [英] Excel Validation list created from a large array

问题描述

目标我必须为用户创建一个下拉列表以滚动浏览并选择一个项目.这些项目是公司的合同，存储在我们的目录中.有成千上万的.

The objective I have to create a dropdown list for the user to scroll through and select an item. The items are the company's contracts, as stored in our directories. There are thousands of them.

尝试我的第一个尝试是使用一个子目录，该子目录会进入我们的目录，并将每个合同文件夹的名称写在 sheet f_param的 column L中.然后，我使用以下代码创建验证列表:

The attempts My first attempt was to use a sub which goes through our directory and writes the name of each contract's folder in column L on sheet f_param. I then used the following code to create the validation list:

With f_param.Range("cell_titreprojet").Validation
    .delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:="=Paramètres!$L$3:$l$1406"
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
End With

效果很好，但是我想将名称存储在 array 中，然后直接使用 array 的 values 填充列表会更好.

It worked very well, but I felt like storing the names in an array, and then directly using the array's values to populate the list would be better.

我以这篇文章为参考:使用VBA的Excel验证下拉列表

I used this post as a reference: Excel Validation Drop Down list using VBA

我设法使用包含以下代码的目录从包含10个项目的目录中创建了验证列表:

I managed to create the validation list from a directory containing 10 items without problem using this code:

Sub Example2()
Dim objFSO As Object
Dim objFolder As Object
Dim objSubFolder As Object
Dim i As Integer
Dim liste() As String


'Create an instance of the FileSystemObject
Set objFSO = CreateObject("Scripting.FileSystemObject")
'Get the folder object
Set objFolder = objFSO.GetFolder("O:")
i = 0
'loops through each file in the directory and stores their path
For Each objSubFolder In objFolder.subfolders
    If IsNumeric(Left(objSubFolder.Name, 1)) Then
        'print folder path
        ReDim Preserve liste(i)
        i = i + 1
    End If
Next objSubFolder


With Feuil2.Range("A1").Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
        Operator:=xlBetween, Formula1:=Join(liste, ",")
End With
End Sub

问题

当我从目录O :(经过测试的10个文件夹)更改为H :(这是我想要的一个)时， array 正确填充了(我在其中打印了1403文件夹名称一个 column 进行检查)，但是在创建列表期间，我收到了一个 error (使用先前发布的相同代码，该代码在较小的数组中起作用).有人知道为什么吗?

When I change from directory O: (the tested one, 10 folders) to H: (which is the one I want), the array is filled correctly (I printed the 1403 folder names in a column to check), but I recieved an error during the list's creation (using the same code posted previously, that worked with a smaller array). Does anyone have an idea why?

错误为1004(大致翻译为法语):由应用程序或对象定义的错误.

The error is 1004 (roughly translated from French) : Error defined by application or object.

推荐答案

此页面说根据列表的构建方式有不同的限制.

This page says that there are different limits depending on how the list is built.

数据验证下拉列表中显示的项目数有限制:该列表最多可以显示工作表上的列表中的32,767个项目.如果您在数据验证对话框(定界列表)中键入项目，则限制为256个字符，包括分隔符.如果您需要的项目更多，则可以创建一个从属下拉列表，按类别细分.

There are limits to the number of items that will show in a data validation drop down list: The list can show up to 32,767 items from a list on the worksheet. If you type the items into the data validation dialog box (a delimited list), the limit is 256 characters, including the separators. If you need more items than that, you could create a dependent drop down list, broken down by category.

这篇关于从大型数组创建的Excel验证列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！