2

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.

Community
  • 1
  • 1
YetAnotherRandomUser
  • 1,320
  • 3
  • 13
  • 31
  • does this get you going in the right direction: http://stackoverflow.com/questions/3587662/how-do-i-sort-a-collection – sous2817 Mar 08 '17 at 20:50
  • Is a Dictionary a Collection? Maybe the other way around? Or are they not related? – YetAnotherRandomUser Mar 08 '17 at 20:57
  • 1
    A dictionary is a type of collection – sous2817 Mar 08 '17 at 21:00
  • Maybe this can be of help to compare dictionaries to collections: http://stackoverflow.com/questions/32479842/comparison-of-dictionary-collections-and-arrays Lists (as you might know them from java) do not exist in VBA. But it seems you are merely missing the "sorting" part of your dictionary before adding it to the ComboBox: http://stackoverflow.com/questions/14808104/sorting-a-dictionary-by-key-in-vba – Ralph Mar 08 '17 at 21:08
  • I'm assuming that you don't have 16,384 potential values for your `ComboBox` (if you do, that's some seriously bad UX going on). There's no reason to test the last 15,000 or so empty cells from the range `B:B` to see if you already have `Empty` in your `Dictionary` - limit your data range to the ***data***. – Comintern Mar 08 '17 at 21:29
  • The length of the list in B:B will change in different instances on the spreadsheet. I do have a known endpoint I can look for, but how do I dynamically set that range? – YetAnotherRandomUser Mar 09 '17 at 10:48

1 Answers1

4

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

Daniel Dušek
  • 13,683
  • 5
  • 36
  • 51
  • Does calling .NET require additional code, libraries, permissions, or compatibility problems with Windows 10, Citrix, or Office 2013? – YetAnotherRandomUser Mar 09 '17 at 01:43
  • .Net framework must be installed on the target system so you can use its classes. I do not understand what you mean with additional code. The code you need is shown in the example. Once you have .net framework installed, in `VBA` code you need to add a reference to the mentioned type library file from .net framework (or perhaps some other version of .net framework which is actually installed) and then you can create instances of those class.`SortedList` is marked with `ComVisibleAttribute` so it can be used via `COM` anywhere. But try it yourself on your target environment. – Daniel Dušek Mar 09 '17 at 05:48