时间戳

首页 > 解决方案 > ExcelVBA - 从数组转换为集合，然后将所述集合插入组合框列表

arrays - ExcelVBA - 从数组转换为集合，然后将所述集合插入组合框列表

问题描述

我有 Sheet1.ComboBox1，我想用一组值填充。该数组存储在 Sheet2 上。此数组是要在 excel 文件中使用的所有客户的列表。所有客户都列在一个列中。

一些客户不止一次出现在列中。它因客户拥有的零件编号数量而异。

我想用这个数组填充一个 Sheet1.ComboBox1，但是，我不想要重复的值。

我在网上读到，我可以将数组转换为一个集合，该集合会自动清除重复项。

我想把这个集合输入到 Sheet1.ComboBox1 中，但是，经过一些研究，我发现集合是只读的......（这个结论我错了吗？）

我看到的一个策略是将客户数组转换为一个集合，然后再转换回一个新的简化数组。希望将这个新数组存储到工作表 3 中，然后将该数组拉入 ComboBox1.List。我已在此尝试下方发布了我的代码。

'Converts collection to an accessible array
  Function collectionToArray(c As Collection) As Variant()
    Dim a() As Variant: ReDim a(0 To c.Count - 1)
    Dim i As Integer
    For i = 1 To c.Count
        a(i - 1) = c.item(i)
    Next
    collectionToArray = a
End Function

Sub PopulateComboBoxes()
Dim ComboBoxArray As New Collection, customer
Dim CustomerArray() As Variant
Dim newarray() As Variant
Dim i As Long

CustomerArray() = Sheet2.Range("A5:A2000")

On Error Resume Next
For Each customer In CustomerArray
    ComboBoxArray.Add customer, customer
Next
    newarray = collectionToArray(ComboBoxArray)
    Sheet3.Range("A1:A2000") = newarray


Sheet1.ComboBox1.List = Sheet3.Range("A1:2000")

我使用 'CustomerArray() = Sheet2.Range("A5:2000")' 并不是因为 Sheet 2 中有那么多行充满了值，而是当更多客户最终添加到列表中时，我涵盖了所有基础。我的 Sheet 2 的总尺寸目前是 A1:A110，但我想在未来证明它。

当我运行代码时，数组成功减少，新数组被放入 Sheet3 中，没有重复。但是，在定义最后一个唯一客户值之后，会重复第一个客户条目。（A46 是最后一个唯一客户，A47:A2000 是同一客户重复）

此外，Sheet1.ComboBox1 仍为空。

有谁能够解释如何限制由 'collectionToArray' 填充的行数，而不是填充所有 2000？

另外，我在填充 ComboBox1 时哪里出错了？我是否缺少导致框填充的命令/功能？

标签： arraysexcelvbacombobox

解决方案

首先，您应该将您的范围动态分配给 CustomerArray ...

With Sheet2
    CustomerArray() = .Range("A5:A" & .Cells(.Rows.Count, "A").End(xlUp).Row).Value
End With

然后，您应该在将项目添加到您的集合后禁用错误处理。由于您没有这样做，因此它隐藏了您在将值分配给列表框时的范围引用不正确的事实，并且您没有使用 Value 属性来分配它们。所以你应该禁用错误处理......

On Error Resume Next
For Each customer In CustomerArray
    ComboBoxArray.Add customer, customer
Next
On Error GoTo 0

然后，将 newarray 传输到工作表时，您需要转置数组...

Sheet3.Range("A1").Resize(UBound(newarray) + 1).Value = Application.Transpose(newarray)

然后，如前所述，您应该使用 Sheet3.Range("A1:A2000").Value 将项目分配给您的列表框。但是，由于 newarray 已经包含项目列表，您可以简单地将 newarray 分配给您的列表框...

Sheet1.ComboBox1.List = newarray

所以完整的代码如下......

Sub PopulateComboBoxes()

    Dim ComboBoxArray As New Collection, customer As Variant
    Dim CustomerArray() As Variant
    Dim newarray() As Variant

    With Sheet2
        CustomerArray() = .Range("A5:A" & .Cells(.Rows.Count, "A").End(xlUp).Row).Value
    End With

    On Error Resume Next
    For Each customer In CustomerArray
        ComboBoxArray.Add customer, customer
    Next
    On Error GoTo 0

    newarray = collectionToArray(ComboBoxArray)

    Sheet3.Range("A1").Resize(UBound(newarray) + 1).Value = Application.Transpose(newarray)

    Sheet1.ComboBox1.List = newarray

End Sub

推荐阅读