如何在VBA(而不是字典)中列出列表? [英] How to make a list in VBA (not a dictionary)?

问题描述

我不熟悉VB或VBA，尽管我熟悉编程.

I'm not that familiar with VB or VBA, though I am familiar with programming.

我正在使用Excel 2016，并尝试根据另一个工作表中的单元格列填充组合框(位于用户窗体中).我需要删除空单元格，重复项，一些已知值，然后对我要处理的内容进行排序.

I'm working with Excel 2016 and trying to populate a combobox (that lives on a user form) based on a column of cells in another worksheet. I need to remove empty cells, duplicates, a few known values, and then sort what I wind up with.

我有一些最有效的代码(除了排序以外，我有其他所有东西)，但是在覆盖它之前我没有保存它.它基于此答案.我不知道如何添加排序.它使用了一个字典，花了5秒钟弹出了一个用户表单，并填充了组合框.列表中只有1000个单元格，因此在我看来，该项目的数据结构字典太复杂了.

I had some code that mostly worked (I had everything but sort), but I didn't save it before overwriting it. It was based on this answer. I could not figure out how to add sorting to it. It used a dictionary and took 5 seconds to pop up a userform and populate the combobox. There are only 1000 cells in the list, so it seems to me that a dictionary is too complex of a data structure for this project.

我正在尝试从这里工作，但是由于变量名称含糊不清，而且我对VB不熟悉，我无法弄清楚它在做什么.

I'm trying to get code from here to work, but I can't figure out what it's doing because the variable names are so vague, and I'm not familiar with VB.

在这一点上，我认为我只需要进行自己的搜索，替换和排序，而不要依赖copypasta.我似乎找不到VBA是否具有List()对象.每个人都在谈论数组和字典.我想使用一种提供排序功能的数据结构(如果存在的话).

At this point, I'm thinking I just need to do my own search and replace and sort and not rely on copypasta. I can't seem to find if VBA has a List() object. Everyone is talking about Arrays and Dictionaries. I'd like to use a data structure that offers a sort function, if one exists.

我应该使用什么样的数据结构?

What data structure should I use for something like this?

我重构了大部分有效的代码.如果不明显，这在UserForm初始化上.

I have reconstructed the code that mostly works. This is on a UserForm Initialize, if that's not obvious.

Private Sub UserForm_Initialize()

    '*Start with empty inputs
    InitialsTextBox.Value = ""

    MakeComboBox.Clear
    ModelComboBox.Clear

    '*Fill the Combo Boxes

    Dim oDictionary As Object
    Dim strCellContent As String
    Dim rngComboValues As Range
    Dim rngCell As Range

    Set rngComboValues = Sheets("BOM").Range("B:B")
    Set oDictionary = CreateObject("Scripting.Dictionary")

    For Each rngCell In rngComboValues
        strCellContent = rngCell.Value

        If Not oDictionary.exists(strCellContent) Then
            oDictionary.Add strCellContent, 0
        End If
    Next rngCell

    For Each itm In oDictionary.keys
        Me.MakeComboBox.AddItem itm
    Next itm

    Set oDictionary = Nothing
    End Sub

编辑/更新

以下答案是一个很好的答案，但需要在运行VBA代码的计算机上安装其他库.尽管这在大多数情况下都可行(.NET仍然很常见)，但对于该项目，我强烈希望没有依赖项和/或保留VBA语言.将要运行的环境可能不喜欢这种东西.

The answer below is a good one, but requires additional libraries to be installed on the machine running the VBA code. While that may work for most cases (.NET is pretty common anyways), for this project I would strongly prefer to not have dependencies and/or leave the VBA language. The environments where this will run may not like such a thing.

推荐答案

我想使用一种提供排序功能的数据结构(如果存在的话).

I'd like to use a data structure that offers a sort function, if one exists.

是的，例如 SortedList .

SortedList表示键/值对的集合，这些键/值对按键排序，并且可以通过键和索引进行访问.

SortedList represents a collection of key/value pairs that are sorted by the keys and are accessible by key and by index.

VBA代码示例:

VBA code example:

添加对C:\Windows\Microsoft.NET\Framework\v4.0.30319\mscorlib.tlb

Sub SortedListDemo()
    Dim sr As mscorlib.SortedList
    Set sr = New mscorlib.SortedList
    sr.Add "D", "D"
    sr.Add "B", "B"
    sr.Add "A", "A"
    sr.Add "C", "C"
    Dim i As Integer
    For i = 0 To sr.Count - 1
        Debug.Print "Key: " & sr.GetKey(i) & ", Value: " & sr.GetByIndex(i)
    Next i
    Set sr = Nothing
End Sub

输出

Key: A, Value: A
Key: B, Value: B
Key: C, Value: C
Key: D, Value: D

更多信息，例如 此处. HTH

这篇关于如何在VBA(而不是字典)中列出列表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！