按字母顺序对组合框进行排序 vba excel

Question

我正在用vba Excel演示一个小程序。我的组合框是从Excel工作表填充的。我想构建一些控件，例如"添加/删除项目"以将其他项目添加到我的组合框，这就是为什么我需要知道组合框上每个项目的行号。我的代码能够做到这一点，但我仍然不知道如何在将项目添加到组合框之前按字母顺序对项目进行排序（请参阅下面的代码）你能帮忙吗？提前致谢

 Sub Fill_EightD_D1_CB1()
With EightD.EightD_D1_CB1
    .ColumnCount = 2        ' 2 colonnes
    .ColumnWidths = "-1;0"  ' dont une de masquée
End With
Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("D1")
Dim LC As Long
Dim i As Long
LC = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
For i = 2 To LC
    If ws.Cells(i, 1) <> "" Then
    EightD.EightD_D1_CB1.AddItem ws.Cells(i, 1).Value
    EightD.EightD_D1_CB1.List(EightD.EightD_D1_CB1.ListCount - 1, 1) = Mid(ws.Cells(i, 1).Address(False, False), 2, 1) 
    End If
Next i
'show always the first element
EightD.EightD_D1_CB1.ListIndex = 0
'Bold Text EightD_D1_CB1
EightD.EightD_D1_CB1.Font.Bold = True
End Sub
 

Answer 1

尝试

Sub Fill_EightD_D1_CB1()
    With EightD.EightD_D1_CB1
        .ColumnCount = 2        ' 2 colonnes
        .ColumnWidths = "-1;0"  ' dont une de masquee
    End With
    Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("D1")
    Dim LC As Long
    Dim i As Long, r As Long, j As Long
    Dim vDB As Variant, vR(), vtemp(1 To 2)
    LC = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    vDB = ws.Range("a2", "a" & LC)
    r = UBound(vDB, 1)
    ReDim vR(1 To r, 1 To 2)
    For i = 1 To r
        vR(i, 1) = vDB(i, 1)
        vR(i, 2) = i + 1
    Next i
    For i = 1 To r
        For j = 1 To r
            If vR(i, 1) < vR(j, 1) Then
                vtemp(1) = vR(i, 1)
                vtemp(2) = vR(i, 2)
                vR(i, 1) = vR(j, 1)
                vR(i, 2) = vR(j, 2)
                vR(j, 1) = vtemp(1)
                vR(j, 2) = vtemp(2)
            End If
        Next j
    Next i
    EightD.EightD_D1_CB1.List = vR
    'show always the first element
    EightD.EightD_D1_CB1.ListIndex = 0
    'Bold Text EightD_D1_CB1
    EightD.EightD_D1_CB1.Font.Bold = True
End Sub

Answer 2

使用支持排序的数据结构首先捕获数据，对其进行排序，然后添加到列表框中。我已经展示了如何在下面的代码中添加和排序。

Sub SortAnArrayList()
    Dim ArrayList As Object
    Dim ArrayItem As Variant
    Set ArrayList = CreateObject("System.Collections.ArrayList")
    With ArrayList
        .Add "b"
        .Add "c"
        .Add "a"
        .Sort
    End With
    For Each ArrayItem In ArrayList
        Debug.Print ArrayItem
    Next
End Sub