对Combobox VBA进行排序 [英] Sort Combobox VBA

问题描述

我一直在想如何对组合框中的值进行排序。

I have been thinking how to sort the values in a combobox.

当我初始化窗体时，我会向ComboBox添加项目，因为值的数量不断增加

I add items to the ComboBox when I initilize the form because the number of values are constantly increasing on a sheet.

我使用下一个代码添加项目：

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

我想把我要添加到ComoBox的值复制到另一个表，并在新表中排序，它工作正常，但它似乎不是一个聪明的选择，意思我创建了另一个工作表，然后复制这些值并对它们进行排序，而不是直接对它们进行排序。

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.

我的问题是，任何人都知道如何从原始工作表中直接进行排序？我不知道任何API所以，请，只有VBA代码。

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.

感谢，如果需要更多信息，请让我知道。

Thanks and if more info is needed, please, let me know it.

PS：我不能直接从原始工作表中排序，因为这个工作表必须有一个静态顺序

PS: I cant sort them directly from the original sheet because this Sheet has to be with a static order

推荐答案

您可以将工作表中的值读入数组，使用代码对其进行排序，然后添加项目。

You can read the values from the sheet into an array, sort this with code and then add the items.

这使用Quicksort：

This code will do this, using a 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

这篇关于对Combobox VBA进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！