Sort combobox values alphabetically

Question

I have a combobox in a userform for excel. What is the easiest way to sort it alphabetically? The values for it are hardcoded in vba and new ones are just added to the bottom so they are not in any kind of order already.

The userform is currently being used so that our users can import data from our database into excel. The combobox is there so they can specify which client data to import.

Answer 1

VBA lacks a built-in sorting function for such things. Unfortunately.

One cheap way that does not involve implementing/using one of the popular sorting algorithms yourself is to use the .NET Framework's ArrayList class via COM:

Sub test()
  Dim l As Object
  Set l = CreateObject("System.Collections.ArrayList")

  ''# these would be the items from your combobox, obviously
  ''# ... add them with a for loop
  l.Add "d"
  l.Add "c"
  l.Add "b"
  l.Add "a"

  l.Sort

  ''# now clear your combobox

  Dim k As Variant
  For Each k In l
    ''# add the sorted items back to your combobox instead
    Debug.Print k
  Next k

End Sub

Make this routine part of UserForm_Initialize. This will of course fail if the framework is not installed.

Answer 2

Creating the array to sort isn't so hard as you might think. See Sorting Mulicolumn Listbox. You can put the List property right into a Variant type, sort that as an array, and dump that Variant Array back into the List property. Still not great, but it's the best VBA's got.

Answer 3

As you are adding them, compare them to the values already in the combobox. If they are less than the item you come across, the replace the item. If they are not less than, then move on until you find something the item is less than. If it cannot find the item, then add it to the end.

For X = 0 To COMBOBOX.ListCount - 1
  COMBOBOX.ListIndex = X
  If NEWVALUE < COMBOBOX.Value Then
     COMBOBOX.AddItem (NEWVALUE), X
     GoTo SKIPHERE
     End If
Next X
        COMBOBOX.AddItem (NEWVALUE)

SKIPHERE: