CODEWITHSUNDEEP
vbaexcel
YetAnotherRandomUser
YetAnotherRandomUser

Reputation: 1408

How to make a list in VBA (not a dictionary)?

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

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.

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.

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.

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?

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

Edit/update

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.

Upvotes: 2

Views: 762

Answers (1)

gembird
gembird

Reputation: 14053

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

Yes exists, for example SortedList from .Net framework can be used.

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

VBA code example:

Add reference to 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

Output

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

Some more information e.g. here. HTH

Upvotes: 4

Related Questions