排序组合框 VBA
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15543399/
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 VBA
提问by themolestones
I have been thinking how to sort the values in a combobox.
我一直在思考如何对组合框中的值进行排序。
I add items to the ComboBox when I initilize the form because the number of values are constantly increasing on a sheet.
我在初始化表单时将项目添加到 ComboBox，因为值的数量在工作表上不断增加。
I use the next code to add the items:
我使用下一个代码来添加项目：
With ComboBox1
lastcell = ThisWorkbook.Sheets("1").Range("F1000000").End(xlUp).Row + 1
For i = 2 To lastcell
.AddItem ThisWorkbook.Sheets("1").Cells(i, 6)
Next i
End With
I thought to copy the values that I am going to add on the ComoBox to another sheet and there sort them in the new sheet, it works fine but it doesn't seem to be a smart option, meaning that I create another sheet and then copy the values and sort them instead of sorting them directly.
我想将要添加到 ComoBox 上的值复制到另一个工作表中，然后在新工作表中对它们进行排序，它工作正常，但似乎不是一个明智的选择，这意味着我创建了另一个工作表，然后复制值并对其进行排序，而不是直接对其进行排序。
My question is, anyone knows how to do it directly from the original sheet? I dont know anything of API so, please, only VBA code. I alredy check on MSDN but I can't figure out how to make it work.
我的问题是，有人知道如何直接从原始工作表中做到这一点吗？我对 API 一无所知，所以，拜托，只有 VBA 代码。我已经在 MSDN 上查看过，但我不知道如何使它工作。
Thanks and if more info is needed, please, let me know it.
谢谢，如果需要更多信息，请告诉我。
PS: I cant sort them directly from the original sheet because this Sheet has to be with a static order
PS：我无法直接从原始工作表中对它们进行排序，因为此工作表必须采用静态顺序
采纳答案by Peter Albert
You can read the values from the sheet into an array, sort this with code and then add the items.
您可以将工作表中的值读取到数组中，使用代码对其进行排序，然后添加项目。
This code will do this, using a Quicksort:
此代码将使用 Quicksort 执行此操作：
Private Sub UserForm_Initialize()
Dim varRange() As Variant
Dim lngLastRow As Long
Dim i As Long
lngLastRow = Range("F:F").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
varRange = Range("F:F").Resize(lngLastRow).Cells
subQuickSort varRange
Me.ComboBox1.List = varRange
End Sub
Public Sub subQuickSort(var1 As Variant, _
Optional ByVal lngLowStart As Long = -1, _
Optional ByVal lngHighStart As Long = -1)
Dim varPivot As Variant
Dim lngLow As Long
Dim lngHigh As Long
lngLowStart = IIf(lngLowStart = -1, LBound(var1), lngLowStart)
lngHighStart = IIf(lngHighStart = -1, UBound(var1), lngHighStart)
lngLow = lngLowStart
lngHigh = lngHighStart
varPivot = var1((lngLowStart + lngHighStart) \ 2, 1)
While (lngLow <= lngHigh)
While (var1(lngLow, 1) < varPivot And lngLow < lngHighStart)
lngLow = lngLow + 1
Wend
While (varPivot < var1(lngHigh, 1) And lngHigh > lngLowStart)
lngHigh = lngHigh - 1
Wend
If (lngLow <= lngHigh) Then
subSwap var1, lngLow, lngHigh
lngLow = lngLow + 1
lngHigh = lngHigh - 1
End If
Wend
If (lngLowStart < lngHigh) Then
subQuickSort var1, lngLowStart, lngHigh
End If
If (lngLow < lngHighStart) Then
subQuickSort var1, lngLow, lngHighStart
End If
End Sub
Private Sub subSwap(var As Variant, lngItem1 As Long, lngItem2 As Long)
Dim varTemp As Variant
varTemp = var(lngItem1, 1)
var(lngItem1, 1) = var(lngItem2, 1)
var(lngItem2, 1) = varTemp
End Sub
回答by Peter Albert
It depends on circumstances, type and structure of data. But i prefer to do it this way:
You could alternatively use an array and a bubble sort algo :)
modify the code a little bit to suit your case
这取决于环境、数据类型和结构。但我更喜欢这样做：
您也可以使用数组和冒泡排序算法 :)
稍微修改代码以适合您的情况
Option Explicit
Sub WITH_COMBOBOX()
Dim i As Long
Dim arr() As String
Dim lastCell As Long
lastCell = 500
ReDim arr(lastCell)
Call FillAndSortArray(arr)
For i = 2 To lastCell
.AddItem arr(i - 2)
Next i
End Sub
Sub FillAndSortArray(ByRef myArray() As String)
Dim i As Long
For i = LBound(myArray) To UBound(myArray)
myArray(i) = CStr(ThisWorkbook.Sheets(1).Range("F" & i + 2).Value)
Next i
Call BubbleSort(myArray)
End Sub
Sub BubbleSort(ByRef myArray() As String)
Dim i As Long, j As Long
Dim Temp As String
For i = LBound(myArray) To UBound(myArray) - 1
For j = i + 1 To UBound(myArray) - 1
If myArray(i) > myArray(j) Then
Temp = myArray(j)
myArray(j) = myArray(i)
myArray(i) = Temp
End If
Next j
Next i
End Sub
回答by Zafar Janjua
for sorting 123 for number
用于对数字进行排序 123
For Each cell In ThisWorkbook.Sheets("sheet1").Range("list1")
Me.ComboBox1.AddItem cell
Next cell
With Me.ComboBox1
For x = LBound(.list) To UBound(.list)
For y = x To UBound(.list)
If .list(y, 0) + 0 < .list(x, 0) + 0 Then
blah = .list(y, 0)
.list(y, 0) = .list(x, 0)
.list(x, 0) = blah
End If
Next y
Next x
End With
for sorting text abcd
用于排序文本 abcd
For Each cell In ThisWorkbook.Sheets("sheet1").Range("list1")
Me.ComboBox1.AddItem cell
Next cell
With Me.ComboBox1
For x = LBound(.list) To UBound(.list)
For y = x To UBound(.list)
If .list(y, 0) < .list(x, 0) Then
blah = .list(y, 0)
.list(y, 0) = .list(x, 0)
.list(x, 0) = blah
End If
Next y
Next x
End With
回答by Zafar Janjua
Try below code :
试试下面的代码：
Sub GetAction()
Dim rng As Range, lastcell As Long
lastcell = Range("F1000").End(xlUp).Row + 1
Set rng = Range("F1:F" & lastcell) ' assuming to start from F1
If Not rng Is Nothing Then
rng.Sort Range("F1")
ComboBox1.ListFillRange = rng.Address
End If
End Sub
