vba 按字母顺序对组合框值进行排序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/860366/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Sort combobox values alphabetically
提问by kadrleyn
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.
我在用户表单中有一个用于 excel 的组合框。按字母顺序排序的最简单方法是什么？它的值在 vba 中进行了硬编码，新的值只是添加到底部，因此它们已经没有任何顺序。
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.
当前正在使用用户表单，以便我们的用户可以将数据从我们的数据库导入到 Excel 中。组合框在那里，以便他们可以指定要导入的客户端数据。
回答by Bryan Gustafson
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:
回答by kadrleyn
It could easily be as follows :
它很容易如下：
Sub fill_combobox()
Dim LastRow, a, b As Long, c As Variant
ComboBox1.Clear
LastRow = Sheets("S1").Cells(Rows.Count, 2).End(xlUp).Row
For x = 2 To LastRow
ComboBox1.AddItem Cells(x, 2).Value
Next
For a = 0 To ComboBox1.ListCount - 1
For b = a To ComboBox1.ListCount - 1
If ComboBox1.List(b) < ComboBox1.List(a) Then
c = ComboBox1.List(a)
ComboBox1.List(a) = ComboBox1.List(b)
ComboBox1.List(b) = c
End If
Next
Next
End Sub
I have used in this template : Add Items Into The Userform Combobox In Alphabetic Order
我在这个模板中使用过：按字母顺序将项目添加到用户表单组合框中
回答by Dick Kusleika
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.
创建要排序的数组并不像您想象的那么难。请参阅排序多列列表框。您可以将 List 属性直接放入 Variant 类型，将其作为数组排序，然后将该 Variant Array 转储回 List 属性。仍然不是很好，但它是最好的 VBA。
回答by Tomalak
VBA lacks a built-in sorting function for such things. Unfortunately.
VBA 缺少针对此类内容的内置排序功能。很遗憾。
One cheap way that does not involve implementing/using one of the popular sorting algorithms yourself is to use the .NET Framework's ArrayListclass via COM:
一种不涉及自己实现/使用流行的排序算法之一的廉价方法是ArrayList通过 COM使用 .NET Framework 的类：
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.
将此例程作为UserForm_Initialize. 如果未安装框架，这当然会失败。
回答by shahkalpesh
This uses ADO library, which I guess will be available on most computers (with Excel installed).
这使用了 ADO 库，我想它可以在大多数计算机上使用（安装了 Excel）。
Sub SortSomeData()
Dim rstData As New ADODB.Recordset
rstData.Fields.Append "Name", adVarChar, 40
rstData.Fields.Append "Age", adInteger
rstData.Open
rstData.AddNew
rstData.Fields("Name") = "Kalpesh"
rstData.Fields("Age") = 30
rstData.Update
rstData.AddNew
rstData.Fields("Name") = "Jon"
rstData.Fields("Age") = 29
rstData.Update
rstData.AddNew
rstData.Fields("Name") = "praxeo"
rstData.Fields("Age") = 1
rstData.Update
MsgBox rstData.RecordCount
Call printData(rstData)
Debug.Print vbCrLf & "Name DESC"
rstData.Sort = "Name DESC"
Call printData(rstData)
Debug.Print vbCrLf & "Name ASC"
rstData.Sort = "Name ASC"
Call printData(rstData)
Debug.Print vbCrLf & "Age ASC"
rstData.Sort = "Age ASC"
Call printData(rstData)
Debug.Print vbCrLf & "Age DESC"
rstData.Sort = "Age DESC"
Call printData(rstData)
End Sub
Sub printData(ByVal data As Recordset)
Debug.Print data.GetString
End Sub
Hope this gives you enough background to get started.
FYI - This is a disconnected recordset (simpler version of .net dataset for in memory tables).
希望这能给你足够的背景知识来开始。
仅供参考 - 这是一个断开连接的记录集（用于内存表的 .net 数据集的简单版本）。
